Forum Discussion
Solved
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 with a bright, bold border (using conditional formatting) but can't find any way to do this because of the way it has been set out. Can anyone please help?
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 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.
- 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).
