Date Help

[sm@gc]

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.

[SamT]

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

[sm@gc]

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.

[p45cal]

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.

[sm@gc]

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:

[p45cal]

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().

[Paul_Hossler]

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

[Aussiebear]

@ Paul, what is Juneteenth?

[Paul_Hossler]

@ 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 (https://en.wikipedia.org/wiki/Juneteenth)

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

[Paul_Hossler]

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)

30071


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

30072


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

[p45cal]

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

[snb]

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

[sm@gc]

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.

[Paul_Hossler]

@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


30073



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

[p45cal]

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.

[SamT]

Case Else: If Day(Date) = 2 Then
.Value = Date -3
Esle
.Value = Date
End If
End Select

[sm@gc]

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.
:clap: