Return different value based on date

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:

If the cell is blank, return as "Pending",
If the date is < = today, return as "Overdue",
If the date is > today, return as "Current",
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!!

Find more posts tagged with

IF(

dates

Accepted answers

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.