Excel - Flow calculation - Number of days absent within a set reporting week

Generally speaking, you can calculate the number of working days between two dates using the NETWORKDAYS function. This takes three arguments:

1. Start date
2. End date
3. Holidays (an optional range of public holidays which are subtracted from the calculation)

So, in a simple calculation of total working days absent between start date and end date for your rows, and assuming you are using standard UK public holidays (I assume from your date format, but you can use any list of holidays):

=NETWORKDAYS(A2,IF(B2="",TODAY(),B2),$I$2:$I$8) 

Here I've said calculate the working days between the start date and end date, or if the end date is blank, between the start date and today.

In reference to your actual question, I think you want to calculate the number of absent days within a particular reporting week. So, you actually want to calculate the difference between the maximum of {the week start date and the absence start date} and the minimum of {the week end date and the absence end date}, using the holidays as specified above.

So, I put each reporting week as a different column next to each row, then used this formula, where the holidays are in column I as before.

=IF(NETWORKDAYS(MAX($A14,C$13),MIN($B14,C$13+6),$I$2:$I$8)<0,0,NETWORKDAYS(MAX($A14,C$13),MIN($B14,C$13+6),$I$2:$I$8)) 

You can see that in weeks where there was a bank holiday but the person was absent all week, it only counts 4 days absent since the bank holiday is "standard". If you don't want to consider bank holidays in the calculation, just remove the third parameter from the NETWORKDAYS function.

Since this formula will eventually return a negative in weeks after the absence has ended, I've used IF to say if the net working days is negative, then just return 0.