I need to add one more condition to my existing formula that is comparing dates

1

Need to change "Unsatisfactory " to if Submission Date is blank and today’s date is 30 days more than expected date and If the Submission date is more than 10 working days after the Expected date.
I have below mentioned the current formula and rules, i need it edited with the above condititon

Current formula
IF(AND(ISBLANK(thisRow.[Expected date]),ISBLANK(thisRow.[Submission Date])),“Enter Dates”,
IF(ISBLANK(thisRow.[Submission Date]),“Unsatisfactory”,
IF(thisRow.[Expected date]=thisRow.[Submission Date],“Meets Expectations”,
IF(AND(NETWORKINGDAYS(thisRow.[Expected date],thisRow.[Submission Date])>0,NETWORKINGDAYS(thisRow.[Expected date],thisRow.[Submission Date])<=10),“Below Expectations”,
IF(NETWORKINGDAYS(thisRow.[Expected date],thisRow.[Submission Date])>10,“Unsatisfactory”,
IF(NETWORKINGDAYS(thisRow.[Expected date],thisRow.[Submission Date])<=0,“Exceeds Expectations”,“”))))))

The current formula is with the below conditions:

  1. Unsatisfactory:
    If the Submission date is more than 10 working days after the Expected date or if submission date is blank
  2. Below Expectations:
    If the submission date is between 1 to 10 days after the the Expected date.
  3. Meets Expectations:
    If the Submission date is the same as the expected date
  4. Exceeds Expectations:
    If the submission date is before the expected date
2

I solved the above query by adding another column that calculates todays date using the formula “today()”.
And using this column to compare with the expected date to find how long the submission date is empty. Formula below

image
image413×579 43 KB

IF(
AND(
ISBLANK(thisRow.[Expected date]),
ISBLANK(thisRow.[Submission Date])
),
“Enter Dates”,
IF(
AND(
ISBLANK(thisRow.[Submission Date]),
NETWORKINGDAYS(thisRow.[Expected date], thisRow.Today) > 30
),
“Unsatisfactory”,
IF(
AND(
ISBLANK(thisRow.[Submission Date]),
NETWORKINGDAYS(thisRow.[Expected date], thisRow.Today) <= 30
),
“Submission date missing”,
IF(
thisRow.[Expected date] = thisRow.[Submission Date],
“Meets Expectations”,
IF(
AND(
NETWORKINGDAYS(
thisRow.[Expected date], thisRow.[Submission Date]) >0,
NETWORKINGDAYS(
thisRow.[Expected date], thisRow.[Submission Date]) <=10
),
“Below Expectations”,
IF(
NETWORKINGDAYS(
thisRow.[Expected date], thisRow.[Submission Date]) >10,
“Unsatisfactory”,
IF(
NETWORKINGDAYS(
thisRow.[Expected date], thisRow.[Submission Date]) <=0,
“Exceeds Expectations”,
“”)))))))

3

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