Forum Discussion
Tracking Delay Day
Hello Guys,
Please help I want to track the delay day based on received date.
1. Let say I have 3 days to review the document if I send out on time I want the cell in column "DELAY (DAYS)" input text as "No".
2. And if I send out late I want the cell to count as delay day in number or it still show the delay day even not yet sent out but already overdue.
Kiripong Assuming the left most header cell is in A1, try the following formula in E2 and copy it down.
=MAX(0,TODAY()*(D2="")+(D2*(ISNUMBER(D2))-C2-3))If you want to make it a bit more dynamic, change the REMARKS column to contain the number of days allowed for review only. Then you can can replace the "-3" at the end with "-F2". This would allow for different numbers of review days allowed for different documents.
25 Replies
Kiripong Assuming the left most header cell is in A1, try the following formula in E2 and copy it down.
=MAX(0,TODAY()*(D2="")+(D2*(ISNUMBER(D2))-C2-3))If you want to make it a bit more dynamic, change the REMARKS column to contain the number of days allowed for review only. Then you can can replace the "-3" at the end with "-F2". This would allow for different numbers of review days allowed for different documents.
Riny_van_Eekelen , thanks Riny really appreciate your help so if it's not delay can we replace from "0" to text. One more thing if we want to count only working day exclude Sat & Sun so 5 days per week may you advise.
Kiripong To include only work days, use this one:
=MAX(0,TODAY()*(D2="")+(D2*(ISNUMBER(D2))-WORKDAY(C2,3)))Same thing here. Replace "3" with "F2" to make it more dynamic.
What do you mean by "replace from "0" to text"? Would you like to display, e.g. "On Time" in stead?
If so, use a custom number format like 0;;"On Time"
Working on the Mac version right now, but the picture is similar for the PC version.
