Sickness Calendar

[Krash7000]

Hi All,

I'm after a bit of help if possible.

I have a list of employees absence's with start date of absence in column A, and end date of absence in column B.

What I want to do is have a list of dates for the year and work off how many employees were off each day.

E.g - Employee A was off from 01/01/20 until 05/01/20, and employee B was off on 04/01/20 - 04/01/20. Data should show as below:
01/01/20 - 1
02/01/20 - 1
03/01/20 - 1
04/01/20 - 2
05/01/20 - 1

 

[eirikdaude]

This is fairly easily solved with

COUNTIFS

. Assuming the data for FROM is in column A and TO is in column B, you can put the dates for the year in e.g. column D and use a formula like:

=COUNTIFS($A$1:$A$2;"<="&$D1;$B$1:$B$2;">="&$D1)

The result should be something like what you see in the screenshot below:

 

[Krash7000]

Thanks for such a quick response! I tried to copy and paste that formula as you had it, but it weirdly doesn't seem to like it when I do it. I am clearly missing something. Sorry!

 

[eirikdaude]

Does your locale use , to separate arguments, instead of ;?

 

[Krash7000]

Does your locale use , to separate arguments, instead of ;?

I think so. When I write forumlas I use a comma , to separate range from criteria etc.

 

[Krash7000]

I think so. When I write forumlas I use a comma , to separate range from criteria etc.

Sorry, your right. I have changed the formula based on your comment. =COUNTIFS($A:$A,"<="&$D1,$B:$B,">="&$D1) seems to work! Thanks!