Previous Topic Next Topic

Excel "IF" function with DATE in test cell

Closed
LNTP3 - Aug 16, 2016 at 11:34 PM
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
 - Aug 25, 2016 at 11:02 AM
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

Answer 1 / 1
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
 514
Aug 25, 2016 at 11:02 AM
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
0