Multiple start and end dates

[Emilyloo]

Hi there,

I'm having trouble producing an absence calendar for my employees. I have multiple start and end dates for holidays but would like to show all holidays on a single line sequentially.
I've attached an example of my problem (I think...first time using this) data showing absence entries on the left and a rolling calendar on the right. I don't know if both of my absences could appear on the same line on the right...please help!

thanks
Emily

[CK76]

You can use COUNTIFS.

Something like...
=IF(COUNTIFS($B$2:$B$3,$F$2,$C$2:$C$3,">="&G$1,$D$2:$D$3,"<="&G$1)>=1,"A","")

See attached.

[Emilyloo]

Thank you so much, it works perfectly!

you've saved me tons of time

[JohnTopley]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[Emilyloo]

Hi,

I thought this solved the problem but, when I enter periods of absence that last longer than one day, nothing shows up at all.

Any ideas?

[CK76]

Woops, looking at it now. I had the sign reversed for start date and end date.

Amend formula to below.
=IF(COUNTIFS($B$2:$B$3,$F$2,$C$2:$C$3,"<="&G$1,$D$2:$D$3,">="&G$1)>=1,"A","")

[Emilyloo]

Ah brilliant!

Thanks again!

[Emilyloo]

Ah brilliant!

Thanks again!