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
- If cell contains a date then return value - Best answers
- If a cell has text then return value ✓ - Excel Forum
- 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 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
555
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 2015-2025), 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 2015-2025), 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
