Calendar tracker in different colours

For Green

Please Login or Register  to view this content.

Similar for Red.

Note: For the last 3 columns of date, I use formula to get the last date of each month automatically. 2024 is leaf year, but your old data did not have day 29.

Hi bebo021999,

Thanks for your help.

I have more questions about this:

Why did you use the formula =DATE(2024,ROWS($4:4),COLUMNS($A:A)) for the dates on the “Calendar” sheet? Did you use it just to get all the days faster rather than typing one by one? Can´t you just put all the dates without the formula?


What formula would I have to use for the conditional formatting if I added more names to the list and if I added more dates too?


Many thanks

"Can´t you just put all the dates without the formula?" > Yes
"What formula would I have to use for the conditional formatting if I added more names to the list and if I added more dates too?"
1. Convert the range of data on the Log sheet into an Excel table
2. Define named ranges for each column in the table
3. For the red conditional formatting rule: =COUNTIFS(Name,$D$3,Dates,G4,Reason,"Disciplinary")
4. For the green conditional formatting rule: =COUNTIFS(Name,$D$3,Dates,G4,Reason,"Holiday")
5. To add to tbl_Log select the last cell in the Reason column and press the Tab key
Let us know if you have any questions.

"Do I have to select G4 since it’s the first cell in the array G4:AK15 on the Calendar sheet?" > If you want to edit one of the rules, you could either select cell G4 or the whole range G4:AK15
"To add to tbl_Log select the last cell in the Reason column and press the Tab key. Do you mean to add an extra row to the table?" > Yes

"1. I would have to select the range G4:AK15, then select conditional formatting (range where I want to apply it)" > Correct.
"2. Put the function below? Ie. G4:AK15 instead of G4?" > You just need to put in G4. Since G4 is a relative reference the formula will evaluate for each cell in the range as if it were a cell formula that had been dragged across and down.
=COUNTIFS(Name,$D$3,Dates,G4,Reason,"Sanction")
I don't feel as if =ISNUMBER(FIND(Dates, Range of dates on the Calendar sheet)) will work.
Try the following instead: =COUNTIFS(Dates,G4)
Let us know if you have any questions.

All clear now!

Many thanks

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

Excel treats zeros as FALSE and any value other than zero as TRUE.
If the name in cell D3, and the date in cell G4 match a row on the Log sheet that has "Disciplinary" as a reason then COUNTIFS will yield a value of 1 which is treated as TRUE.
If the name in cell D3, and the date in cell G4 do not match a row on the Log sheet that has "Disciplinary" as a reason then COUNTIFS will yield a value of 0 which is treated as FALSE.
Let us know if you have any questions.

That is correct.

Thanks again,

The only thing that I don´t understand.



With regards to G4 is it because it´s the first cell in the range so the conditional formatting will evaluate all the cells in the range?

Yes, the red rule is: =COUNTIFS(Name,$D$3,Dates,G4,Reason,"Disciplinary")
If it were a cell formula and you dragged the fill handle down it would become: =COUNTIFS(Name,$D$3,Dates,G5,Reason,"Disciplinary")
If it were a cell formula and you dragged the fill handle to the right it would become: =COUNTIFS(Name,$D$3,Dates,H4,Reason,"Disciplinary")
The same thing happens for each cell in the "Applies to" range because G4 is a relative reference.
G$4, $G4 or $G$4 would behave differently based on whether the row, column or both were locked.
Let us know if you have any questions.

If you are talking about editing a preexisting rule, then I have found it best to either select the first cell or the entire range.
Note that the 365 version may operate differently than the 2019 and earlier versions.

Yes, when writing a rule using a formula like those in the file, then start with the first cell in the range.