Forum Discussion

Sarah Feeney's avatar
Sarah Feeney
Copper Contributor
Aug 15, 2018
Solved

conditional formatting when wrong date format entered

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 -...
excel
Formulas and Functions
  • Matt Mickle's avatar
    Matt Mickle
    Aug 15, 2018

    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

     

     

vijaykumar shetye's avatar
vijaykumar shetye
Brass Contributor
Aug 16, 2018

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

 

Resources