If date is less than, greater than, or blank formula.

Cristina Ayala

I am creating a formula that will display Completed, Upcoming, or Prerecorded based on the dates in Column 2021 Session Dates. When I use the formula below, any row with no date comes back as Completed even though I have added the IF ISBLANK formula to output Prerecorded. I don't get an error in the field, just not getting the desired output. Any assistance is appreciated.

=IF(TODAY(0) > [2021 Session Date]@row, "Completed", IF(TODAY(0) < [2021 Session Date]@row, "Upcoming", IF(ISBLANK([2021 Session Date]@row), "Prerecorded")))

Bassam Khalil

Hi @Cristina Ayala

Hope you are fine, please try the following formula and convert it to column format formula:

=IFERROR(IF(ISBLANK([2021 Session Date]@row), "Prerecorded", IF(AND(ISDATE([2021 Session Date]@row), TODAY() > [2021 Session Date]@row), "Completed", IF(AND(ISDATE([2021 Session Date]@row), TODAY() < [2021 Session Date]@row), "Upcoming"))), "")

the following screenshot shows the result:

Bassam Khalil

Hi @Cristina Ayala

Hope you are fine, please try the following formula and convert it to column format formula:

=IFERROR(IF(ISBLANK([2021 Session Date]@row), "Prerecorded", IF(AND(ISDATE([2021 Session Date]@row), TODAY() > [2021 Session Date]@row), "Completed", IF(AND(ISDATE([2021 Session Date]@row), TODAY() < [2021 Session Date]@row), "Upcoming"))), "")

the following screenshot shows the result:

Cristina Ayala

This worked! Thank you so much, Bassam! Seems like I need to brush up on IFERROR function.

Bassam Khalil

@Cristina Ayala

Excellent, Have a nice weekend 😀