Forum Discussion
Date Format
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")
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")
