Checking for dates in Excel can be a very interesting problem as Excel stores dates as numbers. For example, 4-Jul-15 is stored as 42189.
To elaborate further on this, 1-Jan-1900 is treated as 1. The difference between a date and 1-Jan-1900 +1 is the serial number of that date. hence, in case of 4-Jul-15, it is 42189.
Hence, if you write 42189 and 4-Jul-15 in two different cells, Excel has no built-in mechanism to differentiate between these two.
Excel can not tell whether the written value is a date or not. All the date operations which you can do with 4-Jul-15, you can do with 42189 also.
There are many possible workarounds but none of them are perfect. The only way is to use VBA for this. Don't get intimidated by VBA. This is fairly simple, only 3 lines of codes. Just follow the below steps –
1. ALT+F11 to open VBA Window.
2. Look on left side, there is Project Explorer Window. Locate your workbook name. Workbook name will be in parenthesis and preceded by word VBA project.
3. You may have modules already in your VBA Project. But I am presuming, you will have none as this article is mostly for non-VBA types. Irrespective of the fact, whether module is there or not, follow next step.
4. Right Click on your VBA Project > Insert > Module
5. If there is something existing in your Module, remove it.
6. Copy and Paste following 3 lines in your module –
Function IsDate(cell) As Boolean
IsDate = VBA.IsDate(cell)
End Function
7. Now, you can simply say =ISDATE(A1) if your date is in A1 in your workbook in any worksheet and if the cell is date, you will get TRUE otherwise, you will get FALSE.
NOTE – You will have to save your workbook as .xlsm to use the above function.
Hi,
I am afraid that even that is not 100% for sure. Enter e.g. '5/3 in a cell and it will be evaluated as date.
I agree it could be but which year? In the immediate window: year(vba.IsDate("5/3")) returns 1899 which in fact is a year that Excel don't know or better expressed as a year before the sequential date of Excel starts.
IMO, you have to include at least a check on the year.
Regards
JP Degroote aka JP Ronse
IsDate works perfectly allright. If you give "? Year("5/3")" without quotes in Immediate Window, you will get 2015 i.e. current year only. Now coming back to year(vba.IsDate(“5/3″)). IsDate("5/3") will always return boolean TRUE only. Hence, in effect, you are writing Year(1) which is 1899. If you have Year(0) where 0 means false it is still 1899. The reason is that in VBA, day 1 is 31-Dec-1899 and day 0 is 30-Dec-1899. You can check this by following expressions in Immediate window
? Year(0)
? Month(1)
? Day(1)
? Year(1)
? Month(0)
? Day(0)
In Excel
0 – 0-Jan-1900
1 – 1-Jan-1900
2 – 2-Jan-1900
In VBA
0 – 30-Dec-1899
1 – 31-Dec-1899
2 – 1-Jan-1899
THANK YOU!! I have always been scared of the VBA, and you showed that it really is that easy. Thank you.
How do I use this Function in another Function?
e.g.,
=COUNTIF(ISDATE(U21:AC21),TRUE)
I get a #VALUE! error message in the cell instead of a count.