How can I exactly count NETWORKDAYS in multiple date ranges, after a specific date?

[SJB629]

Hello all,

I'm trying to create a spreadsheet that tracks staff absence in a rolling 12-month period, but I can't work out how to calculate the exact number of days in that rolling period.

On the details tab, I have the following cells (with each absence recorded on a separate row):
- A3: Staff name
- B3: First day of absence
- C3: Last day of absence.
I can calculate the number of working days between those two dates, using the following formula in cell D3: =NETWORKDAYS(B3,C3)

On a summary page, I can count the total number of days absent (ever), using the following formula: =SUMIF(A:A,"Staff member name",D:D)

What I can't get my head around, using that method, is how to calculate days absent within the last year only. I could do it where I only count absences that started within the last 12 months: =SUMIFS(D:D,A:A,"Staff member name",B:B,">" & TODAY()-365). However, that won't take into account the exact number of days' absent, if 12 months ago (to the day), they were mid-way through a period of absence.

Any ideas how to create a formula (or array?) that can there calculate the number of NETWORKDAYS between a series of first and last days, after a given date, rather than relying on counting a summary of NETWORKDAYS?

Many thanks!

 

[Tetra201]

Maybe

=LET(d,EDATE(TODAY(),-12),SUMPRODUCT(--(A3:A999="Staff member name"),--(C3:C999>=d),NETWORKDAYS(IF(B3:B999>d,+B3:B999,d),+C3:C999)))