We talked about Article 19 – How to Check if a cell contains a date. Now, in this article, I will talk about checking the cell for time.
The way Excel stores dates as numbers, in the same way it stores time as numbers only. Only change is in range. The numbers get stored between 0 to 1. Hence, 6 AM is stored as .25, 12 PM is stored as 0.5 and so on.
Now, if you put 6:00 or 0.25 in a cell, Excel will not have a way to tell whether this is a time stored or a number stored.
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 1 line of code. 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 IsTime(Cell) As Boolean
IsTime = (Left(Cell.NumberFormat, 4) = "h:mm" And VarType(Cell) = vbDouble)
End Function
7. Now, you can simply say =IsTime(A1) if your time is in A1 in your workbook in any worksheet and if the cell is time, you will get TRUE otherwise, you will get FALSE.
NOTE – You will have to save your workbook as .xlsm to use the above function.