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)
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.