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