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]

07/02/2021 (Start), 07/02/2021 (End).

Is that 07 Feb or July 02? 07 Feb is a Sunday (not a workday) so Networkdays will not count it.

 

[maxim642]

Sorry, I just picked that date at random. It was supposed to be a weekday. So, let's say 15th Nov 2021 as an example.

 

[jasonb75]

With weekdays your formula gives me a result of 1 when the start and end date are the same.

 

[maxim642]

Hmm, you're correct. The formula is working as intended. I assumed because there were a lot of records with the same start/end date that weren't being picked up that the formula wasn't working. I didn't realise those are all weekends.

If I want to include weekends, what's the simplest way to amend the formula? Basically, what I don't want to use NEWORKDAYS, instead simply count all days?

 

[jasonb75]

The safest way is to use networkdays.intl with a 7 day working week.

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

I've simplified the DATE(...) part a bit for you as well but your original way would still work with this formula.

 

[maxim642]

The safest way is to use networkdays.intl with a 7 day working week.

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

I've simplified the DATE(...) part a bit for you as well but your original way would still work with this formula.

Thank you for helping with this but it's still not showing the single records.

I've uploaded a screenshot of the formula and dates. For Feb-21 (which is 01/02/21) it should show 1.

 

[maxim642]

Thank you for helping with this but it's still not showing the single records.

I've uploaded a screenshot of the formula and dates. For Feb-21 (which is 01/02/21) it should show 1.

I also tried using your slightly amended formula but it still returned a zero.

 

[jasonb75]

Part of the formula is missing, try using the one from my reply.

 

[maxim642]

Part of the formula is missing, try using the one from my reply.

Yeah, it's still not working. No doubt I'm doing something really silly, but I can't see where the error is.