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

[dkmartin]

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

 

[Trevor_S]

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

 

[sergioMabres]

Hi Martin,
Here is an idea that may work for your problem

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

<tbody>
</tbody>

Here is a test file https://1drv.ms/x/s!AovCE1fDrrdSnF4W9cEqt8kopIPR?e=jFiJwp

Cheers
Sergio