I have a spreadsheet with a column containing dates in DD/MM/YYYY format.
I have applied Conditional Formatting to change the cell colour to grey once the date has passed.
However when the date passes the cell colour changes to grey, but the date also changes to a 5 digit number (DayNum?), e.g. 18/01/20 becomes 43848.
How can I change the cell colour and retain the DD/MM/YYYY date format?
Hello,
you need to change the cell style being applied to the cell(s) by means of Conditional Formatting. Most probably your cell style used through Apply Style drop down in your Condition settings of Conditional Formatting is set to:
Category Number
Format General
(use Styles Manager (F11) to change the style properly)
Hope that helps.
Thanks, that fixed it.
F11 a revelation - I’ll be using that again and again.
If it is helpful please consider to click the check mark (
) next to the answer. Thanks in advance …