I have been using a nested formula that uses weekday for weekends, workday for the holidays, and edate to return a date the following month that gets adjusted by the first two functions. The problem is, my formula adjusts for weekends but not holidays. I have tried formatting differently, checking the references, and always to no avail. I will upload a template of the master spreadsheet for analysis.
My formula for returning a date the following month that will not yield a weekend or Federal Holiday (thus, when the banks are closed) is as follows:
=IF(WEEKDAY(WORKDAY(EDATE($'Start Dates'.J9,1),0,$C$3:$C$26))=7,WORKDAY(EDATE($'Start Dates'.J9,1),0,$C$3:$C$26)-1,IF(WEEKDAY(WORKDAY(EDATE($'Start Dates'.J9,1),0,$C$3:$C$26))=1,WORKDAY(EDATE($'Start Dates'.J9,1),0,$C$3:$C$26)-2,WORKDAY(EDATE($'Start Dates'.J9,1),0,$C$3:$C$26)))
The way Im reading this is, in the first line of text, if the weekday=7, in other words, Saturday, we subtract 1 to kick it back to a friday. The workday function gives me the start date and holiday parameters, and the edate function makes the result exactly one month in the future. The second line of text kicks the resulting date back two days in the case that the future date falls on Weekday=1, a Sunday, and failing that, it employs the condition to advance it based on a day the following month. So in other words, any return date that falls on a weekend gets kicked back to the previous Friday, where the Workday function is *supposed * to take the holidays into account and advance it accordingly. It doesn’t.
The formula I have copied returns a date 4 September of this year from the starting date of 4 Aug from reference Start Dates cell J9. 4 Sep is Labor Day even though I double-checked to make sure that is in the list of Holidays, where I have highlit 4 Sep to make it easier to note the discrepancy.
The formula works as far as not returning a date on a weekend, but is ignoring the holidays I have defined, on C3:C26 on the calculations page, where the work is being performed. The Calculations page gets the start date for the bill/subscription for the previous month on the Start Dates page.
As you can see from the spread, I have highlighted the occurrences where the formula fails to compensate for the Holidays I have assigned for this year and 2024. My question is, why does the Workday function fail to take those dates into account, and how do I compensate?
Thank you.
Any assistance is greatly appreciated
Template.ods (58.3 KB)
