need help with formula to set up a warning after 2 work days
Results 1 to 3 of 3

Thread: need help with formula to set up a warning after 2 work days
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default need help with formula to set up a warning after 2 work days

    Please advise. I am pretty green to formulas. I have created a spread sheet for query letters which are considered late after 48 hours. Monday through Fridays are only time counted (if letter sent Friday noon, it would be late Tuesday at 1:00 P.M).
    my J column is running current date, my E column (E2) is for date when query is mailed, my F (F2) column will be for the 48 hour "late" if the condition is not met.
    dkmartin

  2. #2
    Board Regular
    Join Date
    Oct 2015
    Location
    South Wales
    Posts
    549
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: need help with formula to set up a warning after 2 work days

    The 48 hour deadline is calculated as
    =WORKDAY(E2,2)+(E2-INT(E2))

    The first bit adds two working days to E2, however it only deals with days not times. So the second bit works out the time of day in column E, and adds it back on.

    Note that this formula won't be aware of public holidays that fall during the week, so won't add an extra day to the deadline for them.

    You mention that column J has the current date - you don't need that on each row, as the current date/time is obtained using the function =NOW(). So to check if the item is late, amend the formula above to:
    =IF(NOW()>(WORKDAY(E2,2)+(E2-INT(E2))),"Late","Not Late")

  3. #3
    Board Regular sergioMabres's Avatar
    Join Date
    Feb 2013
    Location
    Córdoba, Argentina
    Posts
    920
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: need help with formula to set up a warning after 2 work days

    Hi Martin,
    Here is an idea that may work for your problem
    Code:
    For Column F =IF((NETWORKDAYS.INTL(E2,J2,1)-1)*24-HOUR(E2)+HOUR(J2)>48,"late","")
    For instance for a set of data
    Mailed Late Current
    7/5/2019 12:00 7/9/2019 1:00
    7/5/2019 12:00 7/9/2019 7:00
    7/5/2019 12:00 late 7/9/2019 13:00
    7/5/2019 12:00 late 7/9/2019 19:00
    7/2/2019 11:00 7/4/2019 0:00
    7/2/2019 11:00 7/4/2019 6:00
    7/2/2019 11:00 late 7/4/2019 12:00
    7/2/2019 11:00 late 7/4/2019 18:00
    7/2/2019 11:00 late 7/5/2019 0:00
    Here is a test file https://1drv.ms/x/s!AovCE1fDrrdSnF4W9cEqt8kopIPR?e=jFiJwp

    Cheers
    Sergio
    Last edited by sergioMabres; Jul 6th, 2019 at 05:57 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •