Excel "IF" function with DATE in test cell

[Closed]
Report
-
Posts
2805
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 14, 2021
-
Hello,
I am trying to use the IF function with a date in the 2 logic test cell; If the 1st logic test cell has a date then I want the word "IN TRANSIT" and if doesn't just leave it blank. Then if the 2nd logical test has a date I want the word "RECEIVED" to show, and if it doesn't i want the word "IN TRANSIT". The formula that I have been trying to get to work is shown below. Please help. Thank you so much.

=IF(AND(AL2=DATE,"IN TRANSIT"),(AM2=DATE,RECEIVED,""))

1 reply

Posts
2805
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 14, 2021
482
Hi LNTP3,

What if AL2 has no date, but AM2 does?

See if this is what you are after:
Start by implementing a UDF (user defined function):
Open VB by hitting Alt+F11 > Insert module and paste the following in the big white field:
Function CheckDate(rng As Range) As Boolean
CheckDate = IsDate(rng)
End Function

Now try a formula like this:
=IF(CheckDate(AL2)=TRUE,"IN TRANSIT",IF(CheckDate(AM2)=TRUE,"RECEIVED","IN TRANSIT"))

Best regards,
Trowa