Excel formula to check if cell contains a date
Aniruddha
-
Updated on May 30, 2022 at 11:56 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 - May 30, 2022 at 11:55 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 - May 30, 2022 at 11:55 AM
Related:
- Excel check if cell is date
- Excel check if cell contains date - Best answers
- Excel if cell contains date - Best answers
- Security check preventing login - Facebook Forum
- Security check preventing log in - Facebook Forum
- Excel date format dd.mm.yyyy - Guide
- Security check preventing - Facebook Forum
- Excel if cell is date ✓ - Office Software Forum
1 reply
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 16, 2023
545
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
