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

dkmartin

New Member
Joined
Jul 6, 2019
Messages
1
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
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Trevor_S

Well-known Member
Joined
Oct 17, 2015
Messages
604
Office Version
2016
Platform
Windows
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

Well-known Member
Joined
Feb 24, 2013
Messages
946
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
MailedLateCurrent
7/5/2019 12:007/9/2019 1:00
7/5/2019 12:007/9/2019 7:00
7/5/2019 12:00late7/9/2019 13:00
7/5/2019 12:00late7/9/2019 19:00
7/2/2019 11:007/4/2019 0:00
7/2/2019 11:007/4/2019 6:00
7/2/2019 11:00late7/4/2019 12:00
7/2/2019 11:00late7/4/2019 18:00
7/2/2019 11:00late7/5/2019 0:00

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

Cheers
Sergio
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,096,176
Messages
5,448,808
Members
405,532
Latest member
ganimachani

This Week's Hot Topics

Top