Network days by month across multiple months

[123rickfear]

Hi,

I am creating an absence reporting dashboard.

If an employee has an absence start date and end date that are more than 1 month apart then I am stuck.

what I would like is to show how many network days by month they have missed.

for example

Start Date 28/01/2018
End Date 12/08/2018

I have formula to show how many network days they missed for January =networkdays($F1188,eomonth($F1188,0))
I have formula to show how many network days they missed for August =networkdays($G1188-day($G1188)+1,$G1188,0)
I now need to show how many network days they missed for February - July.

so the desired result would be 3 in the first column (I have this already), then 20,22,21,23,21,22 in the next columns

any ideas?

 

[steve the fish]

Put start date in A1. End date in A2.

Place this in C1:

=DATE(2018,COLUMNS($A$1:A1),1)

Place this in C2:

=MAX(0,NETWORKDAYS(MAX($A$1,C$1),MIN(EOMONTH(C$1,0),$A$2)))

Drag both formulas across. See if that works out.