Exclude, nights, weekends from date/time calculation

Hello,
 
I need to calculate the time difference between 2 date/time columns.  The challenge is that I need to exclude evenings (after 5 p.m.), weekends (Saturday, Sunday) and U.S. Bank holidays. Is there a formula that I can plug into my existing formula to do this?

Please see the attached file and thanks in advance for all of your help!
Book-1.xls
angelfromaboveAsked:
Who is Participating?
 
byundtConnect With a Mentor Commented:
So if you put the three parts of the formula together:
Total number of working hours equals:
number of working hours assuming a full first day and ignoring the last day
plus working hours on last day until task ended
minus working hours on first day before task started

(NETWORKDAYS(A2,B2,H$2:H$10)-1)*("17:00"-"7:30")       number of working hours assuming a full first day and ignoring the final day

+IF(NETWORKDAYS(B2,B2,H$2:H$10),MEDIAN(MOD(B2,1),"7:30","17:00"),"17:00")    plus number of working hours on final day until task ended

-MEDIAN(NETWORKDAYS(A2,A2,H$2:H$10)*MOD(A2,1),"7:30","17:00"))   minus number of working hours on first day before task started
0
 
tailoreddigitalCommented:
0
 
[ fanpages ]IT Services ConsultantCommented:
Hi,

I need to calculate the time difference between 2 date/time columns.  The challenge is that I need to exclude evenings (after 5 p.m.), weekends (Saturday, Sunday) and U.S. Bank holidays. Is there a formula that I can plug into my existing formula to do this?

When does a morning "start" (if an evening commences at 5pm)?

BFN,

fp.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
angelfromaboveAuthor Commented:
Let's just say that morning starts at 7:30 a.m. for these purposes.
0
 
barry houdiniConnect With a Mentor Commented:
Try listing your holiday dates in H2:H10 then use this formula in row 2

=(NETWORKDAYS(A2,B2,H$2:H$10)-1)*("17:00"-"7:30")+IF(NETWORKDAYS(B2,B2,H$2:H$10),MEDIAN(MOD(B2,1),"7:30","17:00"),"17:00")-MEDIAN(NETWORKDAYS(A2,A2,H$2:H$10)*MOD(A2,1),"7:30","17:00")

custom format as [h]:mm and copy down and you'll get the elapsed time in hours including just 07:30 to 17:00 on working days. Using that formula I get 766:01 for your last example

regards, barry
0
 
angelfromaboveAuthor Commented:
Perfect, thank you!  Can I just ask one more thing?

If the time value in the 2nd column of cells is blank, in other words the task is still in progress, I am getting ###### (pound signs) as the returned value.  Is there any way to say that if that cell is blank, to return the string "still in progress"?

Thanks!
0
 
byundtConnect With a Mentor Commented:
To trap a blank cell, just put barryhoudini's formula inside an IF statement:
=IF(B2="","still in progress",(NETWORKDAYS(A2,B2,H$2:H$10)-1)*("17:00"-"7:30")+IF(NETWORKDAYS(B2,B2,H$2:H$10),MEDIAN(MOD(B2,1),"7:30","17:00"),"17:00")-MEDIAN(NETWORKDAYS(A2,A2,H$2:H$10)*MOD(A2,1),"7:30","17:00"))
0
 
angelfromaboveAuthor Commented:
Great byundt, it worked!
0
 
angelfromaboveAuthor Commented:
Hi Barry, can you explain each component of the formula?

I will award the points and accept your solution shortly. I just want to make sure that I have a good understanding and I can explain it to someone..  For example, I don't know what Median and MOD are and what their function is in the formula?

Thanks!!!!
0
 
byundtConnect With a Mentor Commented:
MOD(B2,1)      Divides B2 by 1, then returns the remainder (fractional part of the original number). Because time is the fractional part of a date/time serial number, MOD(B2,1) is a clever way of getting just the time part of B2.

MEDIAN picks the middlemost number in a set, so MEDIAN(MOD(B2,1),"7:30","17:00") would pick
7:30 if B2 is before 7:30
the actual time if B2 is between 7:30 and 17:00
17:00 if B2 is after 17:00

NETWORKDAYS returns the number of working days, from start through finish, but excluding weekends and holidays. If you start and finish on the same working day, then NETWORKDAYS returns 1, unless that day occurs on a weekend or holiday--in which case NETWORKDAYS returns 0.
NETWORKDAYS(A2,B2,H$2:H$10)-1)*("17:00"-"7:30")  is the number of working hours in those full workdays. You subtract 1 from NETWORKDAYS because you need to account for partial hours on start and finish days separately.

If B2 is on a weekend or holiday, then NETWORKDAYS(B2,B2,H$2:H$10) returns 0.
Likewise, if A2 is a weekend or holiday, then NETWORKDAYS(A2,A2,H$2:H$10) returns 0.
If either is a working day, then NETWORKDAYS returns 1.

IF(NETWORKDAYS(B2,B2,H$2:H$10),MEDIAN(MOD(B2,1),"7:30","17:00"),"17:00")
If B2 is a working day, then return MEDIAN(MOD(B2,1),"7:30","17:00"). Otherwise, return 17:00

MEDIAN(NETWORKDAYS(A2,A2,H$2:H$10)*MOD(A2,1),"7:30","17:00")
If A2 is a working day, then return MEDIAN(start time, 7:30, 17:00)
If A2 is not a working day, then MEDIAN(0, 7:30, 17:00) will return 7:30
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.