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
Hello,


Hi, I am trying to write a formula to check if there is DATE value in a particular cell.
If there is a DATE value, need to say "DELETED", if it is blank, then we need to say its NIL

i tried IF conditions but nothing working. Can someone guide?


System Configuration: Windows / Chrome 101.0.4951.67
Related:

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
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:
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

1