Forum Discussion

Kiripong's avatar
Kiripong
Copper Contributor
Jun 04, 2020
Solved

Tracking Delay Day

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...
excel
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Jun 04, 2020

    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's avatar
Kiripong
Copper Contributor
Jun 04, 2020

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.

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Jun 04, 2020

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.

  • Kiripong's avatar
    Kiripong
    Copper Contributor
    Jun 04, 2020

    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)
    • Riny_van_Eekelen's avatar
      Riny_van_Eekelen
      Platinum Contributor
      Jun 04, 2020

      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.

       

      • Kiripong's avatar
        Kiripong
        Copper Contributor
        Jun 05, 2020

        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.com&usp=drive_fs 

Resources