Return different value based on date

vaue

Hi, I'm trying to write a formula to return different values based on the reference date ([Next Calibration/PM Date]@row) compared to today's date. I want the equation to:

1. If the cell is blank, return as "Pending",
2. If the date is < = today, return as "Overdue",
3. If the date is > today, return as "Current",
4. If the date is between today and today(30), return as "Upcoming"

This is the equation that I have so far:

=IF([Next Calibration/PM Date]@row = "", "Pending", IF([Next Calibration/PM Date]@row <= TODAY(30), "Overdue", IF([Next Calibration/PM Date]@row > TODAY(), "Current", IF((TODAY(29) > [Next Calibration/PM Date]<TODAY(), "Upcoming"))))) 

But I can't seem to get the "Upcoming" condition right.

Thank you!!

Samuel Mueller

Hey @vaue try this

=IF([Next Calibration/PM Date]@row = "", "Pending", IF([Next Calibration/PM Date]@row <= TODAY(), "Overdue", IF([Next Calibration/PM Date]@row <= TODAY(30), "Upcoming", "Current")))

Mostly you just needed to switch the current condition and the upcoming condition because the current condition you had would have applied always before the upcoming condition