Allocate amount into monthly columns-formula fails for specific years - NETWORKDAYS issue?

I've been using an incredibly helpful spreadsheet (attached here) but it seems to fail specifically for the years 2022 and 2023. My steps are:

1) In the "Start Here" tab, change Allocation Table Start Date to 1/1/22
2) In the "Weekdays" tab, change dates for one of the rows to 1/1/22-12/31/22

Now the Total (column W) doesn't match the planned amount (column B).

This problem disappears if the end date is 12/30, or if the year isn't 2022 or 2023. I am baffled and dismayed. Can anyone diagnose this? Thank you!

this formula adds an extra day because of the highlighted condition so working days for January are 22 rather 21 from NETWORKDAYS with dates 0f 1st Jan to 31st Jan

=$G11*((MAX(NETWORKDAYS($C11,H$10),0)-MAX(NETWORKDAYS($C11,EOMONTH(H$10,-1)),0))-(MAX(NETWORKDAYS($D11,H$10),0)-MAX(NETWORKDAYS($D11,EOMONTH(H$10,-1)),0))+(EOMONTH(H$10,0)=EOMONTH($C11,0)))

in the hope of finding why the formula fails for 2022, ive been doing some testing (Excel for Mac 2021).

for 2022, if the dates on Weekdays are set to:

1 jan 2022 - 31 jan 2022 = NO problem
1 nov 2022 - 30 nov 2022 = NO problem
1 oct 2022 - 31 oct 2022 = NO problem

1 oct 2022 - 31 dec 2022 PROBLEM for october
1 nov 2022 - 31 dec 2022 PROBLEM for november
1 dec 2022 - 31 dec 2022 PROBLEM for december

so it seems there is something going on with the totals whenever the final date is 31 dec 2022

i have tested years from 2000 - 2030, and this problem occurs in the following years:

2000 > 31 dec is a Sun
2005 > 31 dec is a Sat
2006 > 31 dec is a Sun
2011 > 31 dec is a Sat
2016 > 31 dec is a Sat
2017 > 31 dec is a Sun
2022 > 31 dec is a Sat
2023 > 31 dec is a Sun
2028 > 31 dec is a Sun

Notice that these are all the years where the last day of the year is a Saturday or Sunday (A)

i used this perpetual calendar:
https://r-knott.surrey.ac.uk/PerpetualCalendar.html

Screen Shot 2022-02-09 at 12.06.37 pm.png

well... further testing has shown the problem occurs for ANY period where the last day of the period is a Saturday or Sunday... so dont use the formula above.. it is false hope... sorry

back to the drawing board and make a new formula.


Ok.. i think its fixed now

using this new formula:

Please Login or Register  to view this content.

Pl see file. In H11 copied across.

Please Login or Register  to view this content.

Yes, @janmorris . Thanks for pointing it out.
I have checked and corrected.
Corrected formula.

Please Login or Register  to view this content.

Ss far as I can judge, this is completely redundant as it will apply to whatever is the starting month: there is no obvious rationale for it so simple remove from the formula


+ (EOMONTH(H$10,0)=EOMONTH($C11,0)))

The OP is in Florida, where it is currently 0410. I am sure (s)he will respond when they are up and at it. It isn't unusual for someone to post a query and come back to see what has been suggested 24 hours later.

it adds a day when necessary, so taking it out can cause an imbalance.

It ALWAYS adds a day whatever the starting month ($C11): the dates in row 10 are end of month so the test will always be true when the date (month) in row 10 is the same as month in $C11. So with start date of 01/06/2022 result is 23 days not 22 (from network,days) in June.

Why is it added at all as WEEKDAYS should equal F11?

The example at Excel_University is NOT using NETWORKDAYS so adding the EOMONTH check IS redundant.

Please try at H11

=MAX(0,NETWORKDAYS(MAX($C11,EOMONTH(H$10,-1)+1),MIN(H$10,$D11)))*$G11

O.K!!! but it is wrong!

Thanks so much everyone, and apologies for the late reply! Thanks especially to janmorris for the detailed testing, and kvsrinivasamurthy for the pithy implementation. This was a tricky one

Edit: Bo_Ry, I appreciate your version as well.

Thanks for feedback.