Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Exclude, nights, weekends from date/time calculation

Posted on 2013-05-13
10
Medium Priority
?
1,527 Views
Last Modified: 2013-05-15
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
0
Comment
Question by:angelfromabove
10 Comments
 
LVL 23

Expert Comment

by:tailoreddigital
ID: 39163663
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39163907
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
 

Author Comment

by:angelfromabove
ID: 39165375
Let's just say that morning starts at 7:30 a.m. for these purposes.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 500 total points
ID: 39165852
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
 

Author Comment

by:angelfromabove
ID: 39166771
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
 
LVL 81

Assisted Solution

by:byundt
byundt earned 1500 total points
ID: 39166942
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
 

Author Comment

by:angelfromabove
ID: 39167059
Great byundt, it worked!
0
 

Author Comment

by:angelfromabove
ID: 39167065
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
 
LVL 81

Assisted Solution

by:byundt
byundt earned 1500 total points
ID: 39167082
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
 
LVL 81

Accepted Solution

by:
byundt earned 1500 total points
ID: 39167087
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

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Currently, there is an issue with being able to copy values from an external application to a dropdown list in Project Web Access (PWA).  The standard copy and paste methods don't seem to work properly. Here is a way to accomplish this task to s…
In this post, we will learn to set up the Group Naming policy and will see how it is going to impact the Display Name and the Email addresses of the Group.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question