Unknown error causing conditional formatting inaccuracy

Copper Contributor


I have noticed that cell J14 is not formatting correctly based on the date in H14. It should be red but is instead green.


The purpose of the conditional formatting in column J is to show in red if the target date in column H was not met or show in green if the target date was met. 


Red = exceeded target date

Green = met target date 


Please see attached spreadsheet. 


Can anyone see why J14 is not happy?


(The other formatting rules for columns H and K prompt whether the date is occurring this week or this month. This all appears to be working correctly.)




4 Replies


It's because there is no 31st of september. September only has 30 days.


If you set he horizontal alignment of column H to General, you'll see that most values are right-aligned, but H14 is left-aligned. This indicates that H14 is not a valid date.

Why? September has only 30 days, not 31, so 31/09/2023 does not exist.

If you change H14 to 30/09/2023, J14 will become red.

Ha! Of course - thank you!!