Timeline Calculation

Latha Kona

Hello everyone,
I am trying to write a function that gives below output:
If date reviewed on or before the due date or within one week after the due date, the outcome should be "On-time" else "Not On-time"
If date reviewed is blank and the due date has already passed the output should be No. of Days Overdue

If date reviewed is blank and the due date is in future the output should be No. of Days to deadline

I am using below function, but it says #UNPARSEABLE. Can someone help me resolve the issue.
=IF(AND(ISDATE([Date reviewed]@row), OR([Date reviewed]@row <= [Due date]@row, [Date reviewed]@row <= [Due date]@row+7)), "", IF(ISBLANK([Date reviewed]@row), IF([Due date]@row<TODAY(), DATEDIF([Due date]@row, TODAY(), "D"), DATEDIF(TODAY(), [Due date]@row, "D")), ""))


Thank you.

Aser

Hello, would this formula solve your issue?

=IF(ISBLANK([Date reviewed]@row), IF([Due date]@row < TODAY(), (TODAY() - [Due date]@row) + " days overdue", ([Due date]@row - TODAY()) + " days to deadline"), IF(OR([Date reviewed]@row <= [Due date]@row, [Date reviewed]@row <= [Due date]@row + 7), "On-time", "Not On-time"))

Aser

Hello, would this formula solve your issue?

=IF(ISBLANK([Date reviewed]@row), IF([Due date]@row < TODAY(), (TODAY() - [Due date]@row) + " days overdue", ([Due date]@row - TODAY()) + " days to deadline"), IF(OR([Date reviewed]@row <= [Due date]@row, [Date reviewed]@row <= [Due date]@row + 7), "On-time", "Not On-time"))

Latha Kona

Hi Aser,

Yes, the formula works well. Thank you. Can you help me with the below formula and conditions?

I am currently using a different approach. I calculated the status of the task based on date reviewed and due date.
If date reviewed is not blank, then status is completed.

If date reviewed is blank and due date has passed then status is over due

If date reviewed is blank and due date is in future then status is Upcoming

Now based on the status column I am calculating the task timeliness:
1. If status is completed, then it should check the date reviewed column and we will have two conditions:
(i). If date reviewed is before or after one week the due date then task timeliness is On-time

(ii). If date reviewed is after one week the due date then task time liness has to be "late-#days" (example: "late-5days")

2. If status is upcoming, the output has to be the #days for the due date from today

3. If status is overdue the output has to be the #days overdue

I am currently using below formula, but it says #UNPARSEABLE:

=IF(Status@row = "Completed", IF(AND(ISDATE([Date Reviewed]@row), [Date Reviewed]@row > ([Due Date]@row + 7)), "late-" & ([Date Reviewed]@row - [Due Date]@row - 7) & " days", IF(ISDATE([Date Reviewed]@row), "On-time", "late")), IF([Status]@row = "Upcoming", IF(ISDATE([Due Date]@row), [Due Date]@row - TODAY(), ""),IF([Status]@row = "Overdue", IF(ISDATE([Due Date]@row), "late-" & (TODAY() - [Due Date]@row) & " days", ""), )))