How to make "NETWORKDAYS" calculate the last day of month.

[Dex1214]

I have the following formula below to calculate the percentage of business days left in the month where I use 21 as a constant. The formula works perfectly for what I'm trying to do.

However, if i put in the last day of any month in "C177" (ex. 3/31/2011), then i get 0%. Any thoughts on how to fix this?

=MAX(0,MIN(1,(NETWORKDAYS(C177,EOMONTH(C177,0)-1)/21)))

[DonkeyOte]

You're subtracting 1 from your end date so if C177 was 31st March you're calculating:

31/3 to 30/3 -> -2 working days

given your MAX wrapper you get a 0 result.

What are you trying to calculate exactly - is the end date inclusive or exclusive of working day calculation ?

[Dex1214]

DonkeyOte - thank you for responding. the end date should be inclusive.

[DonkeyOte]

I would suggest then to use something along the lines of:

=NETWORKDAYS(C177,EOMONTH(C177,0))/NETWORKDAYS(EOMONTH(C177,-1)+1,EOMONTH(C177,0))

The above would ensure the denominator reflects the number of working days in the month being analysed (rather than constant of 21)
You can of course add public holiday ranges as nec. to NETWORKDAYS

[Dex1214]

Great. Thank you!