Excel's absence schedule template: how do I highlight the current date?

Hi, I am developing an absence schedule for staff at work. In the template (found here: https://templates.office.com/en-us/employee-absence-schedule-tm03987167) I wanted to highlight today's date w...

excel

office 365

Riny_van_Eekelen
Jul 11, 2022
Gem_M This particular template is quite useless when it comes to date intelligence. It has a sheet for every month with columns numbered 1 to 31 (max) depending on the month.
Then the day name (three characters) is calculated based on the year selected, the sheet you are in and the day number. But there are no dates to "see" if you are on todays date. You have to calculate the real date for every column on every sheet and then compare it to TODAY() to highlight it with conditional formatting.
This one will work but you'll have to add the CF rule to every single sheet.
=TODAY()=DATE(CalendarYear,SHEET(),C$6)

It produces a highlight for todays date (July 11, 2022) as shown in the picture below.

Riny_van_Eekelen

Platinum Contributor

Jul 11, 2022

Gem_M This particular template is quite useless when it comes to date intelligence. It has a sheet for every month with columns numbered 1 to 31 (max) depending on the month.

Then the day name (three characters) is calculated based on the year selected, the sheet you are in and the day number. But there are no dates to "see" if you are on todays date. You have to calculate the real date for every column on every sheet and then compare it to TODAY() to highlight it with conditional formatting.

This one will work but you'll have to add the CF rule to every single sheet.

=TODAY()=DATE(CalendarYear,SHEET(),C$6)

It produces a highlight for todays date (July 11, 2022) as shown in the picture below.

Gem_M

Copper Contributor

Jul 14, 2022

Thanks so much, that's worked perfectly. I've applied it to each sheet as suggested. I suppose it's a good interim whilst I look for something better (any suggestions are welcome).

Forum Discussion

Excel's absence schedule template: how do I highlight the current date?

Resources