Jul 11 2022 09:16 AM
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?
Jul 11 2022 11:16 AM
Solution@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.
Jul 14 2022 04:12 AM
Jul 11 2022 11:16 AM
Solution@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.