Unfortunately Excel doesn’t have an ISDATE function. Excel’s macro language, VBA does, but there is no spreadsheet function that let’s you know if a cell contains a date. Well there is a partial workaround and you can also use VBA.
We can use Excel’s CELL function to identify cells that are formatted as dates. This technique is based on a cell’s format and isn’t perfect. Also it doesn’t automatically update when a cell’s format changes because changing formats won’t trigger a recalculation.
Take the list of entries below. In column B we want to identify if column A contains a date.
The formula in cell B2 which has been copied down is
=LEFT(CELL("format",A2))="D"
This extracts the first character from the CELL function and compares it to D.
Column C has the CELL function by itself. The formula in cell C2, also copied down, is
=CELL("format",A2)
Note that cells formatted as dates return codes starting with D.
When you enter a date into a cell Excel will automatically apply a date format. Also entering a value into a cell with trigger a recalculation (after you press Enter).
The above formula works well in most cases, but it has some issues that are discussed below.
Problems
Row 5 is showing TRUE. Even though the cell is blank (like row 9) there is a date format on cell A5. The CELL function returns the format, not the contents of the cell. Cell A9 is bank but it has no format.
We can handle this issue with an amendment to the formula. Column D below has the following formula.
=AND(A2<>"",LEFT(CELL("format",A2))="D")
It’s a bit longer but it does the trick.
Row 13 is a problem. This is formatted with a fairly complex date format, but the format is not recognised by the CELL function as a date – not sure why.
For this technique to work you need to use standard, simple date formats.
There is a VBA solution. The file needs to be saved as a Macro enabled or a Binary file.
Press Alt + F11.
In the new window that opens click the Insert menu and choose Module.
In the window that opens on the right enter this code.
Function IS_DATE(rng) As Boolean IS_DATE = IsDate(rng) End Function |
Then you can use the function IS_DATE function in a cell – see image below. The formula in cell E2 is
=IS_DATE(A2)
This is called a User Defined Function or UDF. This UDF can only be used in this file. To use it in another file you must create it the same way in the other file.
Brilliant, Neale!
What do the notations (D1, D3, G, PO, etc.) in col. C mean?
That is a code for the format that has been applied to that cell. Anything starting with a D means a date format. G is General, P0 means percentage with zero decimals an ,0 means comma format zero decimals.
Thanks for explaining.
What do the notations (D1, D3, G, PO, etc.) in col. C mean?
I am using this to test cell bd3. If there is a date, it will fetch that. If it is not a date, it will use the result of the today() function. Since the only two options are a date or text, this works for me.
=IF(ISERROR(INT(BD3)),TODAY(),BD3)
Basically, it performs a math function on the cell to be tested. If the function works; it’s a date. If not, it is text.
One may be able to improve the test by passing the results of a different function to the iserr function.
Hi Alan
Not sure it works that well for what I was covering, as it treats numbers as dates.
Regards
Neale
This is no good. You can set the “format” of a cell to a date but not have a valid date in it. This function will report it is a “date” when the contents is not a date.
The VBA is the best way to go, but you can also get by most of the time by using the text function in conjunction with INT, since a valid date is represented by a numeric value.
For example, the value of 1/1/2022 is 44562. So if you are checking cell A2:
=(ISERROR(INT(TEXT(A2,”0″)))=FALSE)
This say, convert the value in A2 to text, then convert it to an integer, and if there is no error, then it’s a valid date. Obviously if a number actually is in A2, this would also report TRUE, but you could add an IIF to see if the value is between certain values.
This is a workaround and not perfect. Yes VBA is the best solution.
BTW a date can have a fraction for time, so not all dates are integers.
IIF is a vba function. how do you use it here?
code you please give the macro of the situation you have in mind?
Not sure what difference using IIF instead vs IF would make.
I wrote an article for CPA Aust on UDFs for ISDATE – see the bottom of it.
https://intheblack.cpaaustralia.com.au/technical-skills/excel-tips-create-your-own-spreadsheet-functions
Even for date stored as text, ISDATE returns true. I tried and found it.