I am creating a task tracker, and want a column that depends on the value of the submission dates

1

For example if the “actual submission date” = “Expected submission date” then i would like the status column to say “On time”
if the “actual submission date” > “Expected submission date” then i would like the status column to say “Early”.

How do i implement this formula
image

2

Hi @SmritiDabar,

Set Expected Date and Actual Date columns to the Date type.

Set Status to a Text then in the first cell type = to make it into a Formula.

Use of SwitchIF to generate text based on comparing the dates is what you need.

This code should get you started.
image

SwitchIf(
  IsBlank(thisRow.[Expected Date]) AND IsBlank(thisRow.[Actual Date]),
  "Enter dates",
  thisRow.[Actual Date] = thisRow.[Expected Date],
  "On Time",
  thisRow.[Expected Date] > thisRow.[Actual Date],
  "Behind Schedule",
  thisRow.[Expected Date] < thisRow.[Actual Date],
  "Ahead of Schedule"
)

Find out more here.

All the best.

Dale

3 Likes
3

Thank you @Dale_Cowling !
It really helped

4

Hi @Dale_Cowling ,

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

Below is the formula Used:
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

5

Hi @SmritiDabar,

You can add further SwitchIF arguments.

I have used thisRow.[Expected Date]-thisRow.[Submission Date] <= -14 as the formula. This gets you the date difference in days and checks if it is less than or equal to -14. If the Submission happens before the Expected date the number is positive and if it is after the number is negative. You can try this by adding a column that has a formula of just thisRow.[Expected Date]-thisRow.[Submission Date] to see what it returns for each row.

The new code will look like this:
image

SwitchIf(
  IsBlank(thisRow.[Expected Date]) AND IsBlank(thisRow.[Submission Date]),
  "Enter dates",
  thisRow.[Expected Date]-thisRow.[Submission Date] <= -14,
  "Unsatisfactory",
  (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"
)

Now one very important thing to understand about SwitchIF, and why I have put the new argument in the middle rather than at the end, is that SwitchIF will stop at the first test that returns TRUE.

Prior to the modification it was fine as only one possible test could be correct (return TRUE). Now when a Submission is exceeded by 14 days, both the new 14 day test and the old test of thisRow.[Expected Date] < thisRow.[Submission Date] could return TRUE and it will stop at the first one it finds. If I placed the new test at the end it would never show as it would always stop when the Expected Date is before the Submission Date test and return “Below Expectations”.

Note that if you are going to add further arguments with different day values it will add more complexity. If you are not getting the result you expect the order is likely wrong.

I hope that this makes sense and can help you out.

All the best

Dale

1 Like
6

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