Date Format

[Ron_Hockman1525]

Is there a formula to "detect" a field that is not properly input as a date example 7/31/204

I want to be able to us a conditional format to color code that field if it is not a proper date.

Thank you

[#2]

Depends on where the data are coming from, how the data is showing up in the first place. Assuming you understand how Excel actually stores dates (i.e., 7/31/24 is actually in Excel as the number 45504.00) and assuming the dates that your spreadsheet is going to be dealing with are relatively current, it should at least be possible to write a conditional formatting rule that checks to see if the value in the cell is between 45292 (1/1/24) and today's date (or some future date specified). But you could also use Data Validation to ensure that a date is entered.

[#3 · Accepted solution]

There are multiple factors here:
a) is the TEXT in that cell a valid/recognizable date
b) is the VALUE in that cell a date value
c) is the VALUE in that cell an acceptable date
so in your example if you enter 7/31/204 it is not recognized and therefore entered at TEXT
However if you format that cell as TEXT or enter '7/31/2024 the TEXT is a valid/recognizable date format but the VALUE in the cell is still TEXT therefore certain functions/actions will NOT recognize it
The last case is a number like 45512 is just a number but if you tell excel to format as a Date you will see that VALUE is equal to the date 8/8/2024
so you can check if the entry is a VALUE or text in a recognizable date format using:
=NOT(ISERROR(--[cell]))
or you can check if the VALUE in that cell is already recognized as a VALUE using:
=ISNUMBER( [cell] )
but to check if that VALUE is actually a "valid" date (e.g. check they didn't enter just the year like 2024) you will have to add value range to make sure the value is maybe within a particular window like: >--("1/1/2000") and <--("1/1/2050")

[#4]

Thank you. This is very helpful