conditional formatting when wrong date format entered

[Sarah Feeney]

Hello,

I am working on a spreadsheet that records training records.

the order of rules is as follows

1) If the cell is left blank - turns grey

2) the training must be completed every 2 years - if the date entered is 2 years from todays date -  cell value, less than =EDATE(TODAY(),-24) - turns red

3) if the date entered is 21 months from todays =EDATE(TODAY(),-21) - cell turns amber

4) if the date entered is in the near future (as in training booked ) - cell vale, greater than - =TODAY() it turns green

- I now need that if a wrong date format is entered the cell will go blue.

currently it is going green

the date is supposed to be entered 12/25/2018 and appears on spreadsheet as 25/12/2018

if entered 25/12/2018 it goes green ???  

[#2 · Accepted solution]

In Excel when using comparison operators like <, =, > number values are less than text values.  You can use the VALUE function to convert text values to number values for number comparisons. You can use the TEXT function to convert number values to text values for text comparison.

Since dates are read as numbers in Excel.  (i.e. 08/15/2018 is read as 43327) the text value of "25/12/2018" is > than the number 43327

The best way I can thing to solve your issue is to use Data Validation to ensure dates entered on your spreadsheet are in the correct date range.

Data > Data Validation > Settings > Allow: Date > Select Date Range

[#3]

Hi Sarah Feeney,

I recommend that you enter dates as 25 Dec 2018 or as 25 Dec, rather than 25/12/2018 or 12/25/2018.

If year is not entered, then the current year will be added by Excel.

Also format cells as dd mmm yyyy or dd-mmmm or some other format in which month is displayed as text.

Regards,

Vijaykumar Shetye,

Spreadsheet Excellence,

Panaji, Goa, India

[#4]

Thank you matt that worked :)