Forum Discussion
Tracking Delay Day
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.
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.
Riny_van_Eekelen, Really help a lot and one last question here if I Replace "3" with "F2" to make it more dynamic so then can we make F2 cell automatically count as 3 or 5 (Days) if the cell contain:
- B2 cell contain specific text as "MOBILE BILL" F2 cell will have 3 (Days)
- B3 cell contain specific text as "ELECTRIC BILL" F3 cell will have 5 (Days)
