Jun 03 2020 11:30 PM
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.
Jun 04 2020 12:02 AM
Solution@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.
Jun 04 2020 12:54 AM
@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.
Jun 04 2020 01:11 AM
@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.
Jun 04 2020 01:42 AM
@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:
Jun 04 2020 01:53 AM
@Kiripong Then need a separate table where you have the Description in the first column and the number of days in the second.
Then you enter a formula in F2, like:
=VLOOKUP(B2,<table>,2,FALSE)
... where <table> contains the reference to the table with the descriptions and number of days.
Jun 04 2020 02:40 AM
@Riny_van_Eekelen, Thanks you very much that was awesome my fully day are over now I can sleep well.
Jun 04 2020 11:31 PM
@Riny_van_Eekelen , I tried to use more dynamic "WORKDAY.INTL" to have specific weekend and Holiday yet it didn't come out as I expected can you please advise as I'm not sure maybe I inserted it wrong.
Please click link below to follow:
Jun 05 2020 12:26 AM
@Kiripong Couldn't access the file in your link. You can attach it directly to a response in this forum, if you like.
Can't really tell what you expect the outcome to be. I assume that the WORKDAY.INTL function excludes the holidays from the time it starts counting. That is, June 1 plus two days. Perhaps the attached file can help you determine how to set the parameters. It visualises the case you described.
With the received date on June 1 plus two review days, the delay starts counting on June 3 to June 11, excluding Sunday June 7, returning 8 days delay.
Jun 05 2020 02:52 AM
@Riny_van_Eekelen , can you check my attached file I'm not sure if I use this formula correct or not.
Or can you show me the following formula:
Jun 05 2020 03:01 AM
Jun 05 2020 04:18 AM
Jun 05 2020 06:03 PM
@Riny_van_Eekelen , Everything worked just fine but there's still 1 thing that need to be fixed:
I tried but it didn't work may you advise on this.
Jun 05 2020 09:43 PM
@Kiripong Forget about the old formula. By changing the requirements it no longer does what it was supposed to do in the beginning. Just use the latest formula and wrap it in a MAX statement like this:
=MAX(0,NETWORKDAYS.INTL(C4,D4,11,$H$3:$H$4)-F4)
This will set the delay to 0 if there is no sent-out date. Now if you want something similar to what we had before use this one:
=IF(D2="","-",MAX(0,NETWORKDAYS.INTL(C2,D2,11,$H$2:$H$3)-F2))
It will display "-" if there is no sent date yet, and zero is there was no delay. Use custom formatting as explained before to display zeros as "On Time".
And now that I'm at it, I would recommend you to get rid of merged cells and make use of structured tables for your documents, review times and holidays as in the attached example. Learn more about structured tables here:
Jun 07 2020 11:23 PM
@Riny_van_Eekelen, I really appreciate your help on this but is it possible to count the delay even without sent-out date?
Your very first formula is working fine except no weekend, no holiday was included.
Jun 08 2020 12:27 AM
Sure, no problem. Just replace the "-" by the calculation Today() - ReceivedDate like this:
=IF(D2="",TODAY()-C2,MAX(0,NETWORKDAYS.INTL(C2,D2,11,$H$2:$H$3)-F2))
Jun 08 2020 06:31 AM
@Riny_van_Eekelen, Sorry to bothering but the formula you provided stop counting the weekend and holiday I felt like this last formula not included.
MAX(0,NETWORKDAYS.INTL(C2,D2,11,$H$2:$H$3)-F2
Jun 08 2020 06:59 AM
@Kiripong Don't understand why a formula would suddenly stop working. Can't really help you if I don't see the file you are working with. The formula you pasted seems to have a bracket missing at the end. Perhaps a mistake, though.
Jun 08 2020 07:17 AM
@Riny_van_Eekelen, it was the same file you provided and I just replaced as you told me but it's not working.
Jun 08 2020 07:20 AM
@Kiripong Try the attached file. It's the one I cleaned-up and it uses structured tables.
Jun 04 2020 12:02 AM
Solution@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.