Number of Days between two dates

Occasional Visitor


Can you please help me with the below as I've been racking my brain trying to figure it out. 

I have two dates so take it as leave dates. I want to take these two dates i.e 3/1/2021 - 3/5/2021 and should on another table that for each day there was one employee taking off. Obviously if i have multiple employees taking over laping days the count needs to increase. 


The Date range per employee is on the left. I want to reflect it in a date range on the right. Answer is on the right. 


SD 23/10/20213/15/2021  3/10/20212
SD 43/10/20213/12/2021  3/11/20213
SD 13/11/20213/15/2021  3/12/20213
SD 83/15/20213/15/2021  3/13/20212
SD 23/19/20213/22/2021  3/14/20212
SD 83/19/20213/19/2021  3/15/20213
SD 53/22/20213/28/2021  3/16/20210
SD 13/29/20213/29/2021  3/17/20210
SD 103/29/20214/2/2021  3/18/20210
1 Reply

@Glenith I'm on a European date system so I used dd/mm/yyyy dates in the picture below. The SUMPRODUCT formula should work for you. Not the I used named ranges for the start and end dates.

Screenshot 2022-03-31 at 07.55.33.png