SOLVED

How to write date validation formula

Copper Contributor

I am trying to compare the date inside of Column E to todays date.
1. If today's date is greater than date in Column E then Expired

2. If the date within Column E is within 30 days of Today's date

 

This is what I came up with 

First to check if the Column E value is blank

Second if Today's date is greater than or equal to E65 then Expired

Thirdly Check if Column is greater than today and then check if the input is less Today+30 days then Expiring

 

=IF(ISBLANK(E65),"", IF(TODAY()>=E65, "Expired","", IF(AND(E65>TODAY(),E65<=(TODAY()+30)),"Expiring","")))

 

 

3 Replies
best response confirmed by Hongan1300 (Copper Contributor)
Solution

@Hongan1300 

Perhaps

= IF(
    ISBLANK(E65),
    "",
    IF(
        TODAY() >= E65,
        "Expired",
        IF(
            AND(E65 > TODAY(), E65 <= (TODAY() + 30)),
            "Expiring",
            ""
        )
    )
)
That worked! Still unsure why mine didnt work, was it the spacing?

@Hongan1300 

You had "" as second option in first IF(). With nested IF() second option is always next IF, except very last one.

1 best response

Accepted Solutions
best response confirmed by Hongan1300 (Copper Contributor)
Solution

@Hongan1300 

Perhaps

= IF(
    ISBLANK(E65),
    "",
    IF(
        TODAY() >= E65,
        "Expired",
        IF(
            AND(E65 > TODAY(), E65 <= (TODAY() + 30)),
            "Expiring",
            ""
        )
    )
)

View solution in original post