Excel formula to check if cell contains a date

Aniruddha - Updated on May 30, 2022 at 11:56 AM
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 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

1 reply

TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
514
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

0