Excel formula to check if cell contains a date
Closed
Aniruddha

Updated on May 30, 2022 at 11:56 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022  May 30, 2022 at 11:55 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022  May 30, 2022 at 11:55 AM
Related:
 If cell contains date then return value
 Excel if cell contains date then return value  Best answers
 Excel if cell contains date  Best answers
 If cell = date return yes or no. ✓  Office Software Forum
 Excel: If Date =, then enter a value ✓  Excel Forum
 Excel "IF" function w/ date in test cell ✓  Excel Forum
 If a cell has text then return value ✓  Excel Forum
 If cell A1 has text then cell B2 has today's Date ✓  Excel Forum
1 response
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Updated on May 30, 2022 at 11:55 AM
Updated on May 30, 2022 at 11:55 AM
Hi Aniruddha,
There isn't a function to check if a cell contains a date. See if the following workaround formula works in your case:
=IF(ISERROR(YEAR(A1)),"NIL","DELETED")
When you also enter numbers where you put your dates, this could cause issues. When your dates are relatively close to each other (something like 20152025), we can add in more conditions to the formula like:
=IF(ISERROR(YEAR(A1)),"NIL",IF(AND(YEAR(A1)>2014,YEAR(A1)<2026),"DELETED","NIL"))
Otherwise we have to resort to VBA to create a user defined function:
 Open the Visual Basic Editor by hitting Alt+F11
 Go to the top menus > Insert and click on Module
 Paste the code below in the big white field that appears
 Visual Basic Editor window can now be closed
 In Excel you will now be able to use the function IsThisDate(). Place a cell reference between the brackets and the result will either be "DELETED" or "NIL". Example: =IsThisDate(A1)
This is the code:
Let us know if something is unclear.
Best regards,
Trowa
There isn't a function to check if a cell contains a date. See if the following workaround formula works in your case:
=IF(ISERROR(YEAR(A1)),"NIL","DELETED")
When you also enter numbers where you put your dates, this could cause issues. When your dates are relatively close to each other (something like 20152025), we can add in more conditions to the formula like:
=IF(ISERROR(YEAR(A1)),"NIL",IF(AND(YEAR(A1)>2014,YEAR(A1)<2026),"DELETED","NIL"))
Otherwise we have to resort to VBA to create a user defined function:
 Open the Visual Basic Editor by hitting Alt+F11
 Go to the top menus > Insert and click on Module
 Paste the code below in the big white field that appears
 Visual Basic Editor window can now be closed
 In Excel you will now be able to use the function IsThisDate(). Place a cell reference between the brackets and the result will either be "DELETED" or "NIL". Example: =IsThisDate(A1)
This is the code:
Public Function IsThisDate(mCell As Range) If IsDate(mCell) = True Then IsThisDate = "DELETED" Else IsThisDate = "NIL" End If End Function
Let us know if something is unclear.
Best regards,
Trowa