Count working days and toggle include today or not, taking into account holidays

[D.Lovell]

In the attached example I am counting the number of working days in the current month excluding holidays, where the holiday dates are input by the user. It also lists the number of working days so far, and the number remaining (again also taking into account any holiday dates entered).

I have a couple of issues here that I need help with please if possible.....

Firstly I find myself having to put a -1 in at the end of the formula for total working days some months, but not others... but I can't figure out why this happens.

Secondly, and this is the main thing I want help with... On a given day, the formulas will calculate the number of days to go and days remaining including today. I want the user to be able to decide easily if today should be included or not without having to edit the formulas.
So I added a checkbox for 'include today', and do a plus or minus 1 on the formulas for whether it's checked or not (using a linked cell), and realise its going to be rather more complicated due to having to take into account todays date.

Whether the checkbox is checked or not should effect the number working days left to go, and the number remaining, only if today is a working day, ie. today is not a weekend day or one of the holiday dates input by the user. This surely is doable but I think a little beyond my current skill level.

I think you will get what I mean if you have a go at the example, any help is much appreciated.

[Jonmo1]

You should do the adding/subtracting based on the checkbox on the date within the networkdays function.
NOT on the end result of the function.
And the beginning/end dates within the networkdays should reflect the first and last day of the month (not the day before or after)

Try these (I put the TODAY() function in C1)

Total working days: =NETWORKDAYS(EOMONTH(C1,-1)+1,EOMONTH(C1,0),$A$10:$A$28)
Working days so far: =NETWORKDAYS(EOMONTH(C1,-1)+1,C1-NOT(H2),$A$10:$A$28)
Working days left: =NETWORKDAYS(C1+NOT(H2),EOMONTH(C1,0),$A$10:$A$28)

[pjwhitfield]

For the 2nd part, in I2 put:

=NETWORKDAYS(NOW(),NOW(),A10:A14) * H2

Then add this to your calculation ie where you have the IF statement relating to the checkbox simply add I2

eg B4 should be changed from:

=NETWORKDAYS(TODAY(),EOMONTH(TODAY(),0),$A$10:$A$28)-IF($H$2=TRUE,1,0)

to

=NETWORKDAYS(EOMONTH(TODAY(),-1),TODAY()-1,$A$10:$A$28)+$I2

EDIT Forgot to say, reverse your question ie make it "EXCLUDE TODAY" for the above to work.

[D.Lovell]

Hi Jonbo,

Sorry for my slow reply, yes this is what I meant, thanks for that. With the include today or not though I mean if its not included in working days so far, then it should be included in working days left instead, today should be included in one or the other depending on the checkbox, with the total of those 2 always adding up to the total working days. I think need to change the working days left formula slightly, any ideas?

[Jonmo1]

No worries, that can be adjusted.

But just for clarity
IF the Checkbox IS checked (H2 is TRUE) then today is included in which formula?
If the Checkbox is NOT checked (H2 is FALSE) then today is included in which formula?

[Jonmo1]

Assuming
TRUE = include today in working days so far
FALSE = include today in working days remaining

Should just remove the NOT from the days remaining formula
=NETWORKDAYS(C1+H2,EOMONTH(C1,0),$A$10:$A$28)

[D.Lovell]

Oh I see, yes I get it now, I thought it had something to do with the not, I tried just about everything other than taking it out all together.

This works perfectly now, many thanks for your help

[Jonmo1]

You're welcome.