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 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:
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.
Kiripong Indeed, this is out of context. Better to start a new conversation for in such cases. But never mind. Attached a quick-and-dirty solution for you.
Riny_van_Eekelen, Sorry to bothering and this question was out of the context yet if you can help that would be awesome and to be more dynamic please see below query:
How to joint text string as per Column C2-C5 base on item A, B , C or D between these 2 tables:
Kiripong Good we finally figured it out!
Riny_van_Eekelen, Well well well that was genius thanks for your help really appreciate that.
Actually I live in Southeast Asia and if you happen to travel here you should visit Cambodia then I will give you some brief information such as beautiful beach, tropical forest, Ancient Angkor, etc.,Take care as Covid-19 still around the corner.
Kiripong Sorry, that was an oversight on my part. Obviously, the calculation for the delay without a sent date should be similar to one with a sent date. The only exception is to use TODAY() in stead of Sent date. Please try the attached file again.
Riny_van_Eekelen, It's still not working as I tried to change the date of the highlight row to 1-Jun-20, but the counting was 8 days delay how is that happened when there're 2 holidays + 3 review days + weekend?
If the holiday was deleted the delay would be changed only the row with the sent date.
Kiripong Try the attached file. It's the one I cleaned-up and it uses structured tables.
Riny_van_Eekelen, it was the same file you provided and I just replaced as you told me but it's not working.
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.
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
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))