Solved

# NetWorkDay Day Problem

Posted on 2011-10-06
Medium Priority
371 Views
I have been struggling with this for to long now and I may be making it harder than need be.  I have a 2 date time fields (mm/dd/yyyy hh:mm) and I need to calculate the number of business hours between the 2 dates.  I can do it in Access or Excel.   Thank you!
0
Question by:rcowen00
• 12
• 4
• 3
• +1

LVL 49

Expert Comment

ID: 36925980

What do you define as business hours?

If the first field is:  9/26/11 12:00:00 (noon)

and the 2nd date value is: 9/28/11 14:00:00

What should the correct number be?

0

Author Comment

ID: 36926029
Are you concerned about holidays?  I would like them excluded.

What do you define as business hours?   See my next answer, I'm not sure I need business hours.  If I do then 8am-5pm

If the first field is:  9/26/11 12:00:00 (noon)

and the 2nd date value is: 9/28/11 14:00:00

What should the correct number be?    2.2 (2 days and 2 hours)
0

LVL 74

Expert Comment

ID: 36926095
Well "Days" would certainly be easier...
;-)

The kicker with Allowing for holidays is that you have to create a list of all holidays.
Sounds simple enough, ...right.

Well what about holidays in different countries?
What about Holidays that are based on a "Day" not really a "Date" (Thanksgiving in the US is the 4the Thursday in November)
Will this be only for one year, if not then you have to add holidays for each year...

Just FYI.

;-)

JeffCoachman
0

Author Comment

ID: 36926120
boaq2000, thank you for the insight.  We are based solely in the US so only those holidays apply.  I have in the past used a function that needed to be updated annually with the clients holidays, so that would be fine.
0

LVL 49

Expert Comment

ID: 36926234
Hmmmmm, 2.2?

What kind of a date range would you have between these two fields?  How many days max?

So, the difference between 9/26/11 08:00:00 and 9/28/11 17:00:00 would be: 2.9?

and the difference between 9/26/11 17:00:00 and 9/28/11 08:00:00 would be: 1 (since 08:00:00 on the 28th is just like 17:00:00 on the 27th with regard to business hours)?

0

Author Comment

ID: 36926258
Yes, in a perfect world I would like it to work as described.  There is no max, this is a turn time report and I've seen some go out 66 days.  That is not to say it couldn't go longer.
0

LVL 50

Expert Comment

ID: 36926344
If start date/time is in A2 and end date/time in B2 you can use this formula to total business hours between the two (assuming 8:00 until 17:00)

=(NETWORKDAYS(A2,B2,H\$2:H\$10)-1)*("17:00"-"8:00")+MOD(B2,1)-MOD(A2,1)

format result cell as [h]:mm (note the square brackets)

where H2:H10 contains a list of holiday dates

For your example you'd get 20:00

Note that I assume that start and end times will be within business hours (not at weekend, on holidays or in evening etc.). If start and end times could be outside business hours then the formula may not give correct results - I can provide a more complex formula that will work for that scenario.

regards, barry
0

LVL 74

Expert Comment

ID: 36926356
fyed may have something more elegant, but you can see here as a start:

http://msdn.microsoft.com/en-us/library/dd327646%28v=office.12%29.aspx
0

Author Comment

ID: 36926377
I believe the function you supplied boaq2000 is whole days only.  I do nneed partial days/hours.
0

Author Comment

ID: 36926398
I may have been wrong boaq2000, I am going to review it a little closer.

barryhoudini, the start or end times can be outside of business hours.  I will need to look at effect.

0

LVL 50

Expert Comment

ID: 36926448
OK for any start and end times you can use this version

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

I can post an example sheet if you like

regards, barry
0

Author Comment

ID: 36926462
boag2000 may have it.  I am running the code now.  I actually already had this but did not realize I could change the "d" to "hh".  I will get back to you.
0

Author Comment

ID: 36926609
boag2000, your suggestion works except that when I change the interval from "d" to "hh" I lose the removal of the weekends.  Any suggestions?
0

Author Comment

ID: 36926644
boag2000,  also if I have a start date of 9/12/2011 10:46 and end date of 9/12/2011 11:19 it is counted as one day.
0

LVL 50

Accepted Solution

barry houdini earned 2000 total points
ID: 36926673
FWIW, here's an example of how you can use my suggested formula in Excel,

I used some named ranges, Hols for holiday range, ST for MF start time (08:00) and EN for MF end time (17:00) so the formula becomes this:

=(NETWORKDAYS(A2,B2,Hols)-1)*(EN-ST)+IF(NETWORKDAYS(B2,B2,Hols),MEDIAN(MOD(B2,1),EN,ST),EN)-MEDIAN(NETWORKDAYS(A2,A2,Hols)*MOD(A2,1),EN,ST)

I then used another formula in the next column to convert that to days and hours. The formulas can be combined if you want

Start and end times are randomly generated. Press F9 to re-generate

regards, barry
27383986v2.xls
0

Author Comment

ID: 36926681
barryhoudini,  This is what I got with your suggestion.  It appears off unless I am misunderstanding.  Thank you.

7/28/2011 20:21      8/25/2011 12:22      7.31
8/17/2011 17:25      9/2/2011 16:02      3.33
8/18/2011 20:11      9/14/2011 13:00      5.08
8/18/2011 20:16      8/18/2011 20:25      0.00
8/19/2011 16:04      9/21/2011 9:01      6.08
8/23/2011 12:12      8/23/2011 14:43      0.10
8/23/2011 19:39      8/24/2011 15:41      0.32
8/23/2011 19:52      8/31/2011 19:10      1.13
0

Author Comment

ID: 36926715
Barry, It is a problem on my end.  I am working through it.  Thanks for your patience
0

LVL 50

Expert Comment

ID: 36926730
It's probably a formatting issue - if you format as h:mm then hours will never be higher than 23 (that's clock format). For "elapsed hours" that will show 24+ hours you need to format as I suggested above, with square brackets around the h, custom format as

[h]:mm

See my sample spreadsheet attached above - that's how I have it formatted

regards, barry
0

LVL 49

Expert Comment

ID: 36926841
If the start time or end time is outside the normal business day, what date/time value should be used?

For example, if the start date is: 9/26/11 19:00:00 (7 PM), should that be treated the same as 9/17/11 08:00:00?

The decimal values that Barry's spreadsheet is providing is the the % of a full day, as would normally be the case, but which is not what you described in your post to me at the top of the page, where 2 hours equates to 0.2

0

Author Comment

ID: 36926851
For example, if the start date is: 9/26/11 19:00:00 (7 PM), should that be treated the same as 9/17/11 08:00:00? Yes
0

Author Comment

ID: 36926885
fyed,  Barry's solutions does work for me.  Thanks everyone for your input.
0

## Featured Post

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
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.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a qâ€¦
###### Suggested Courses
Course of the Month13 days, 22 hours left to enroll