Consulting

Results 1 to 17 of 17

Thread: Date Help

  1. #1

    Date Help

    Win10 Pro with O365.

    I'm new the this company as IT manager and have inherited a lot of stuff. We had an Access programmer that was only OK. I've got no access to Access (sorry) to modify any of the code, so I have to use as is.
    I've automated with Power Automate an Access program that a user ran first thing in the am. By having it run before she gets in, I'm saving her about an hour and everyone in the warehouse starts an hour earlier. Total win for them. I basically saved the company about 12 hours the first day it ran. ROI is less than a week.

    The program runs Monday through Friday at 3:00 am and is date driven. The program asks for a date at 4 different times. Currently, I've got a spreadsheet with:
    =IF(WEEKDAY(TODAY())=2, TODAY()-3, TODAY()-1)
    If today is Monday, then subtract 3 for last Friday; otherwise, subtract 1 for yesterday. Works like a charm.

    Our company observes 10 holidays every year. Those dates will change each year.
    Goal:
    To write VBA code in my spreadsheet to accommodate for those specific dates.
    In a testing spreadsheet, I've got E2 thru E11 formatted as date. Each cell has the work day following the specific holiday.
    2/22/2022 was Tuesday following President's Day. My formula would need last Friday's date or today()-4 or 2/18/2022.

    Sub Holiday()
    Dim dtToday As Date
    dtToday = Date
    If dtToday = E2 Then
       Range("A1") = Date - 4
    ElseIf dtToday = E3 Then
       Range("A1") = Date - 4
    ElseIf dtToday = E4 Then
       Range("A1") = Date - 4
    ElseIf dtToday = E5 Then
       Range("A1") = Date - 4
    ElseIf dtToday = E6 Then
       Range("A1") = Date - 4
    ElseIf dtToday = E7 Then
       Range("A1") = Date - 4
    ElseIf dtToday = E8 Then
       Range("A1") = Date - 5
    ElseIf dtToday = E9 Then
       Range("A1") = Date - 4
    ElseIf dtToday = E10 Then
       Range("A1") = Date - 4
    Else
       dtToday
    End If
    'If dtToday = Date Then
    'Range("A1") = "Howdy"
    'End If
    End Sub
    To test, I change my system date to the day after a holiday and run the macro. It only returns the date of today()-4 regardless of the system date. I'm missing something quite easy, just can't see the forest thru the trees.

    I'd hardcode the dates in like:
    If dtToday = 2/22/2022 Then
    Range("A1") = Date - 4
    But VB adds spaces to the date - 2 / 22 / 2022 and hoses the code (or appears to).

    Once working, I'd like to have the macro run when the sheet is opened; but I can work around that.

    Thanks for the help.
    Last edited by Aussiebear; 08-17-2022 at 05:39 PM. Reason: Added code tags to supplied code

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    MS Dates are numbers indicating the number of days since 1/1/1900, regardless of how they are displayed in an application.

    If you want to hardcode a date use
    If dtToday = CDate("2/22/2022") Then
    CDate will convert that String to the proper number.

    Generally speaking, Excel auto converts Dates entered as string to the correct number, then displays them as strings for human consumption. Test by temporarily formatting E2 as a number.

    Sub Holiday()
    'Places holiday appropriate Dates in A1
    'Assumes Code is NOT in Worksheet Code Page
    Dim Sh1 As Worksheet
    Set Sh1 = Sheets("Sheet1") 'Edit Name to suit
    
    With Sh1.Range("A1")
       Select Case Date
          Case Is = Cdate(Sh1.Range("E2")):  .Value = Date - 4
          Case is = CDate(Sh1.Range("E3")):  .Value = Date - 4 
          Case Is = CDate(Sh1.Range("E4")):  .Value = Date - 4
          Case Is = CDate(Sh1.Range("E5")):  .Value = Date - 4
          Case Is = CDate(Sh1.Range("E6")):  .Value = Date - 4
          Case Is = CDate(Sh1.Range("E7")):  .Value = Date - 4
          Case Is = Cdate(Sh1.Range("E8")):  .Value = Date - 5
          Case Is = Cdate(Sh1.Range("E9")):  .Value = Date - 4
          Case Is = Cdate(Sh1.Range("E10")): .Value = Date - 4
          Case Else:                         .Value = Date
       End Select
    End With
    End Sub
    Personally, I would place the Holiday offset values in F2 to F10, then edit the above .Value = Date -4|5 Lines to read
    .Value = Date + Sh1.Range("F2") 'etc


    macro run when the sheet is opened
    Worksheet Code
    Private Sub Worksheet_Activate()
       Module1.Holiday 'Assumes Sub Holiday is in Module1
    End Sub
    macro run when the Workbook is opened
    ThisWorkbook code
    Private Sub Workbook_Open()
       Sheets("Sheet1").Holiday 'Assumes Sub Holiday is in Sheet1's Code page
    End Sub
    Last edited by SamT; 08-17-2022 at 01:28 PM.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    Thank you, so much!
    I've spent way too many hours (of frustration) working on this.
    The only thing missing would be to accommodate for Mondays needing to read Fridays date.
    The Excel formula is:
    =IF(WEEKDAY(TODAY())=2, TODAY()-3, TODAY()-1)

    I'll mess with it, but sure would appreciate that final touch.

    BTW - I was born in Springfield, MO. Since we moved to CA in the early 70's, I haven't been back.
    Use a pun, go to jail.
    With the price of gas, drink beer and save money.

  4. #4
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    You've got O365, so you've got WORKDAY.INTL
    The equivalent of:
    =IF(WEEKDAY(TODAY())=2, TODAY()-3, TODAY()-1)
    is:
    =WORKDAY.INTL(TODAY(),-1,1)
    But it's better than that, if you have a list of holiday dates somewhere you can point to them (and there can be 10 years of them if you want) in the last argument in the formula:
    =WORKDAY.INTL(TODAY(),-1,1,$Q$2:$Q$21)
    That's it.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5
    Thanks.
    After snooping with workday.intl - it's interesting and will probably do the trick.
    Not sure how I would code it as case else:
    Use a pun, go to jail.
    With the price of gas, drink beer and save money.

  6. #6
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    I doubt very much you need Case Else, nor multiple Ifs, I think the function will handle that. What's the Case Else for?
    =WORKDAY.INTL(TODAY(),-1,1,$Q$2:$Q$21)
    where Q2:Q21 contains a list of actual holiday dates, will give you the previous workday every time, taking holidays and weekends into account.

    See attached where some holiday dates in J3:J9
    Some pretend TODAY() dates in column B
    Your formula in column C (some incorrect because no holiday adjustment)
    Workday.Intl in Column D; no change in the formulae but holidays and weekends are taken into account.

    Cells C5:D5 (green) contain formulae you might actually use because they use TODAY().
    Attached Files Attached Files
    Last edited by p45cal; 08-17-2022 at 06:00 PM. Reason: Added sample file
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  7. #7
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    I had a hard time understanding your business rules, but you might consider something like this

    I went back to the real holiday date as a basic, instead of the day after by adding +1, and adjusting the number of step back days but the +1

    Just seemed like a needless complication

    I made it a User Defined Function, since it's easier to use and to test, but it could be incorporated in a sub easily

    President's day was the only one with an answer, so I couldn't check the rest

    Option Explicit
    
    
    Sub drv()
        MsgBox AdjustHoliday(Range("E2:E12"), #8/14/2022#)
    End Sub
    
    
    Function AdjustHoliday(Holidays As Range, Optional D As Date = 0) As Date
        Dim dtToday As Date
        Dim V As Variant
        Dim m As Long
    
    
        If D = 0 Then
            dtToday = Int(Date)
        Else
            dtToday = Int(D)
        End If
    
    
        AdjustHoliday = dtToday
        
        V = Application.WorksheetFunction.Transpose(Holidays.Columns(1))
        
        m = 0
        On Error Resume Next
        m = Application.WorksheetFunction.Match(CDbl(dtToday), V, 0)
        On Error GoTo 0
        
        If m > 0 Then
            AdjustHoliday = dtToday - Weekday(dtToday) - 1
        ElseIf Weekday(dtToday) = vbSaturday Then
            AdjustHoliday = dtToday - 1
        ElseIf Weekday(dtToday) = vbSunday Then
            AdjustHoliday = dtToday - 2
        Else
            AdjustHoliday = dtToday
        End If
    
    
    End Function
    Attached Images Attached Images
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  8. #8
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    @ Paul, what is Juneteenth?
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  9. #9
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Quote Originally Posted by Aussiebear View Post
    @ Paul, what is Juneteenth?
    Juneteenth is a federal holiday in the United States commemorating the emancipation of enslaved African Americans.

    Juneteenth marks the anniversary of the announcement of General Order No. 3 by Union Army general Gordon Granger on June 19, 1865, proclaiming freedom for slaves in Texas.
    Wikipedia

    For test purposes I just grabbed a list of US Federal holidays
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  10. #10
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Quote Originally Posted by p45cal View Post
    I doubt very much you need Case Else, nor multiple Ifs
    Question about WORKDAY.INTL



    Col E has -1 for second parm and Col F has 0 for second parm (just playing around)

    Capture.JPG


    Col E -- I'd have thought that 2/7 Monday would stay 2/7, but it rolls back to Friday 2/4. I assume that it's the -1 parm skipping weekends and ending on Friday



    Same for 2/22, day after Holiday

    Capture1.JPG


    How would you get it to leave non-Holiday Monday through Friday dates as is?
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  11. #11
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Paul,
    MS describe the function with 'Returns the serial number of the date before or after a specified number of workdays'
    The second parameter is the number of WORKDAYs before/after the start date so:
    for column F where that is 0 workdays it's no surprise that the resulting date is the startdate.
    for column E, where that is -1 workdays it gives a result where there is 1 workday between the start date and the result of the formula. Might it help to picture the start date, say Monday, being (in Excel) actually at midnight at the beginning of that Monday, so to return the beginning of the 1 workday before that is midnight at the beginning of the Friday before.
    How would you get it to leave non-Holiday Monday through Friday dates as is?
    Well, maybe you could subtract 1 day from the start date, then have +1 as the second parameter (to add a working day), then test if the start date is the same as the formula result.
    [You could do the reverse, of course, add one to the start date and use -1 as the second paramter]
    You could use these to test if a specific date is a workday, but that's probably easier using:
    =NETWORKDAYS.INTL(C2,C2,1)=1
    Finally, just for info, any time portion in the dates is removed.

    What I like about these two functions is that you can use a string of noughts and ones to define any pattern of weekend days which could find more use today as people's jobs often include only some days at the business premises and some days working from home. So if they needed to estimate when a job would be finished while this job can only be done at the business premises where they're only present on Mondays, Tuesdays and Wednesdays, WORKDAYS.INTL would be their friend
    Last edited by p45cal; 08-18-2022 at 08:22 AM.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  12. #12
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Sub M_snb()
      MsgBox Date - 1 + (Weekday(Date, 2) = 1) + [not(iserr(match(today()-1+(weekday(today()-1,2)=1),e2:e10, 0)))]
    End Sub

  13. #13
    I'm not sure the weekday.intl will work for me.
    I've got SamT's working with 1 exception - a non-holiday Monday needs to show Friday's date.
    Use a pun, go to jail.
    With the price of gas, drink beer and save money.

  14. #14
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    @P45cal

    Good explanation. I updated my weekend adjustment sub to include holidays and the more flexible inputs to the Workday_Intl

    Depending on FirdayBefore, the input date adjusts back to the previous work day. or forward to the next workday

    So far, it seems to work reliabily


    Capture.JPG

    Function dateAdjustWeekends(OriginalDate As Variant, _
        Optional Holidays As Range = Nothing, Optional Weekends As Long = 1, _
            Optional FridayBefore As Boolean = True) As Variant
        
        dateAdjustWeekends = CVErr(xlErrNum)
        On Error GoTo NiceExit
        
        With Application.WorksheetFunction
            If .NetworkDays_Intl(OriginalDate, OriginalDate, Weekends, Holidays) = 1 Then
                dateAdjustWeekends = OriginalDate
        
            ElseIf FridayBefore Then
                dateAdjustWeekends = .WorkDay_Intl(OriginalDate, -1, Weekends, Holidays)
            Else
                dateAdjustWeekends = .WorkDay_Intl(OriginalDate, 1, Weekends, Holidays)
            End If
        End With
    
    
    NiceExit:
    End Function
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  15. #15
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    sm@gc,
    This could be so simple.
    Put the actual holiday dates in E2:E11, NOT offsets.
    Then your choice of one the following bulleted points:
    • Put the formula:
      =WORKDAY.INTL(TODAY(),-1,1,$E$2:$E$11)
      into cell A1. Leave it there, it will update when the sheet recalculates.

    • Run this macro:
      Sub Holiday()
      With Range("A1")
        .FormulaR1C1 = "=WORKDAY.INTL(TODAY(),-1,1,R2C5:R11C5)"
        '.Value = .Value 'optional line to convert the formula result to plain value.
      End With
      End Sub
    • Run the same macro but uncomment the line beginning '.Value by removing the leading apostrophe.

    • Run this macro:
      Sub Holiday2()
      Range("A1").Value = Application.WorkDay_Intl(Date, -1, 1, Range("E2:E11"))
      End Sub


    All macros act on whichever sheet is the active sheet.
    Last edited by p45cal; 08-18-2022 at 01:42 PM.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  16. #16
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Case Else: If Day(Date) = 2 Then
       .Value = Date -3
    Esle
       .Value = Date
    End If
    End Select
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  17. #17
    Guys, thank you so much! P45cal's formula works like a charm. SamT's final post fixes his coding from earlier.
    I can use either.
    I really appreciate all of the input and how quickly it's happened.
    Use a pun, go to jail.
    With the price of gas, drink beer and save money.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •