To add a time limit check to one of the conditions

1

Can you please help with the below

in this formula is there anyway i can add a time limit for “unsatisfactory” rating. That if Submission date has been exceeded by 14 days, it should fall under unsatisfactory.
for “below Expectation” the Submission Date should should be below extension of 14 days.

These are the conditions i need

  • Expected date and Submission Date are blank : Enter dates
  • Submission Date is blank or 15 days more than Expected date: "Unsatisfactory
  • Expected date and Submission Date are equal: “Meets Expectations”
  • Expected date is less than Submission Date: “Below Expectations”,
  • Expected date is more than Submission Date: “Exceeds Expectations”

Below is the formula that i am currently using:
SwitchIf(
IsBlank(thisRow.[Expected date]) AND IsBlank(thisRow.[Submission Date]),
“Enter dates”,
(thisRow.[Expected date]) AND IsBlank(thisRow.[Submission Date]),
“Unsatisfactory”,
thisRow.[Submission Date] = thisRow.[Expected date],
“Meets Expectations”,
thisRow.[Expected date] < thisRow.[Submission Date],
“Below Expectations”,
thisRow.[Expected date] > thisRow.[Submission Date],
“Exceeds Expectations”
)

image
image282×634 25.4 KB

2

To modify your existing formula in Coda to incorporate the new conditions for “Unsatisfactory” and “Below Expectations” ratings, you can use date comparison functions. I added the Days() in the formula

SwitchIf(
    IsBlank(thisRow.[Expected date]) AND IsBlank(thisRow.[Submission Date]),
    "Enter dates",
    IsBlank(thisRow.[Submission Date]) OR thisRow.[Submission Date] > thisRow.[Expected date] + Days(14),
    "Unsatisfactory",
    thisRow.[Submission Date] = thisRow.[Expected date],
    "Meets Expectations",
    thisRow.[Expected date] < thisRow.[Submission Date] AND thisRow.[Submission Date] <= thisRow.[Expected date] + Days(14),
    "Below Expectations",
    thisRow.[Expected date] > thisRow.[Submission Date],
    "Exceeds Expectations"
)

In this formula, the Days(14) function is used to add 14 days to the “Expected date” for comparison with the “Submission Date”.

1 Like
3

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.