How to calculate the days and months between two dates separate

[Spykapa]

Hello , i want to calculate how many days are between to dates without Public Holidays and second from a contract who start at one date and finish to another, how many days is from January to April e.t.c. I attach a file , i want a formula to calculate the yellow cells. Thank you very much

[Special-K]

I dont really wanna get too involved in this as its late Friday afternoon and I'm leaving for the weekend shortly but here's a start

Days between dates

=DATEDIF("1/1/2019","30/4/2019","d")+1

Days between dates excluding Public Holidays

=(DATEDIF("1/1/2019","30/4/2019","d")+1)-SUMPRODUCT((N6:N15>=A16)*(N6:N15<=B16))

[Root_]

For the yellow cells in range A8:G8, try

=NETWORKDAYS.INTL($J$5,$J$6,REPLACE("1111111",FIND(LEFT(A$5,2),"zMoTuWeThFrSaSu")/2,1,0),$N$6:$N$15)

For the yellow cells in range H11:H14, try

=MIN("2019-4-30",$J$6)-MAX("2019-1-1",$J$5)+1

Change the dates in red as needed.

[FlameRetired]

Edited Fixed detail in formula.

For the days in H11:H14 break the Start-End dates out in I11:J14. With the Contract Start - End Date in I10:J10 this in H11 and filled down.

Please Login or Register  to view this content.

[FlameRetired]

A shorter formula for H11:H14

Please Login or Register  to view this content.