I have created a budget that returns a date approximately one month in advance, adjusting for weekends and holidays (typical days the banks aren’t open). This date changes from month to month, depending on what workdays are available 1 month in advance, but it seems that whenever there is a holiday, the furutre date gets advanced by one or more days. This is problematic because I need it to accurately reflect when monthly bills become due - and if it falls on a non-workday, the due date is usually a day before, not 1 or several days after.
I am uploading a template that illustrates the problem, and I do mean that literally. I have the calculated due dates outlined in green, with the discrepancies in black (these are the backgrounds, the text is white), while the starting dates for each entry for several months is outlined in yellow.
A specific example of the formula I’m using is as follows:
=WORKDAY(D28,NETWORKDAYS(D28,EDATE(D28,1))-1,$C$21:$C$45)
where D28 contains the start date, specifically Tue, 15 Aug, 2023, and Tue, 15 Aug, 2023
is the list of holidays that are to be avoided, and that formula returns a date of Mon, 18 Sep, 2023 - 1 workday after I expected. What i’m wondering is, why does it not return a date of Friday, 15 Sep? That’s not a holiday, and the Edate portion of the function, which advances the date ahead one month, results in the the date in question. The “-1” That follows the nested Networkdays(Edate)) function is a a check valve on the Networkdays function, which I believe includes the start date in your function - thus adding a day to the Workday function.
This is not as simple as simply adding “-2” to the function. That makes some days, where this discrepancy does not occur, to revert back one day, thus exacerbating the problem even more.
Any assiatance is greatly appreciated.
Template.ods (36.7 KB)
