Forum Discussion
conditional formatting when wrong date format entered
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
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
Thank you matt that worked :-)
