My formula isn't picking up single day entries for absence.

[maxim642]

=MAX(0,NETWORKDAYS(MAX(S$1,$G18),MIN(DATE(YEAR(S$1),MONTH(S$1)+1,0),$H18)))

In columns G & H I have the start and end dates respectively for when someone is absent from work. Columns S:AD have dates in them (the start of every month, Jan-Dec 2021.

The above formula is picking up the work days in between the dates when there is more than one day in between, but instances where the person is off for only 1 day just result in a "0", e.g. 07/02/2021 (Start), 07/02/2021 (End).

Maybe I need to create a helper column?? Any help is greatly appreciated.

 

[jasonb75]

My formula was based on the one that you provided as an example, I'm going to hazard a guess that it needs to go into the Jan column then be filled to the right for it to work.

 

[maxim642]

Yep, you're correct. I was being an idiot. I forgot I had a filter on my data so I wasn't pasting into the correct cells. Thanks for your patience. It's producing the correct values now. This has been super helpful.

One quick question, what do the x7 zeros at the end of the formula mean/do? Thanks.

 

[maxim642]

Ok, so this is the last question...there are a small handful of records not being picked up (below). They're all the end of the month, Any, quick way around that? Thanks once again for your help.

 

[jasonb75]

They're all the end of the month, Any, quick way around that?

That was me having a brainfart, correct formula (for January row 18) should be

=MAX(0,NETWORKDAYS.INTL(MAX(S$1,$G18),MIN(EOMONTH(S$1,0),$H18),"0000000"))

One quick question, what do the x7 zeros at the end of the formula mean/do?

They set the working week, the 7 zeros represent a week of monday to sunday, 0 denotes a working day, 1 denotes a weekend day to be ignored by the count, so for a normal week of monday to friday you would use "0000011" to exclude saturday and sunday as weekend days.

 

[maxim642]

=MAX(0,NETWORKDAYS.INTL(MAX(S$1,$G18),MIN(EOMONTH(S$1,0),$H18),"0000000"))

Awesome! Thanks again for not only your help but also the clarification of what the formula is fully doing.