Tracking Delay Day

[Kiripong]

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.

[#2 · Accepted 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. 

[#3]

@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.

[#4]

@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.

[#5]

@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)

[#6]

@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.

[#7]

@Riny_van_Eekelen, Thanks you very much that was awesome my fully day are over now I can sleep well.

[#8]

@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.

• I tried to set weekend only Sat or any day.
• I don't know why the holiday not count on 1-Jun-20, if my receive date and holiday start the same day the first day will not count.

Please click link below to follow:

https://drive.google.com/open?id=1Y014UBYRROFckjyYgmlFyRuKcb7UXILu&authuser=kiripong%40oha-global.co... 

[#9]

@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.

[#10]

@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:

1. With the same formula you provided me last time but I want to have specific weekend (Mon, Wed or Fri etc.,)
2. The formula you provide me but exclude Weekend & Holiday.

[#11]

@Riny_van_Eekelen, I checked your formula but there's some issue:
1. If I didn't input the sent date it would count as -43986
2. If I delete the 01-Jun (Holiday) the counting not change.

[#12]

@Kiripong I believe we need to try something else using NETWORKDAYS.INT in stead.

See attached.

[#13]

@Riny_van_Eekelen , Everything worked just fine but there's still 1 thing that need to be fixed:

• If there's no sent-out date the delay would not be counted.
• Is it possible to combine previous formula with new one maybe a little bit adjustment:
  • =MAX(0,TODAY()*(D5="")+(D5*(ISNUMBER(D5))-NETWORKDAYS.INTL(C5,F5,11,$H$3:$H$4)))

I tried but it didn't work may you advise on this.

[#14]

@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:

https://support.office.com/en-us/article/Using-structured-references-with-Excel-tables-F5ED2452-2337... 

[#15]

@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.

[#16]

@Kiripong 

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))

[#17]

@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

[#18]

@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.

[#19]

@Riny_van_Eekelen, it was the same file you provided and I just replaced as you told me but it's not working.

[#20]

@Kiripong Try the attached file. It's the one I cleaned-up and it uses structured tables.