NetWorkDay Day Problem

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!
rcowen00Asked:
Who is Participating?
 
barry houdiniCommented:
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
 
Dale FyeCommented:
Are you concerned about holidays?

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
 
rcowen00Author Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Jeffrey CoachmanMIS LiasonCommented:
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
 
rcowen00Author Commented:
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
 
Dale FyeCommented:
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
 
rcowen00Author Commented:
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
 
barry houdiniCommented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
rcowen00Author Commented:
I believe the function you supplied boaq2000 is whole days only.  I do nneed partial days/hours.
0
 
rcowen00Author Commented:
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
 
barry houdiniCommented:
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
 
rcowen00Author Commented:
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
 
rcowen00Author Commented:
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
 
rcowen00Author Commented:
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
 
rcowen00Author Commented:
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
 
rcowen00Author Commented:
Barry, It is a problem on my end.  I am working through it.  Thanks for your patience
0
 
barry houdiniCommented:
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
 
Dale FyeCommented:
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
 
rcowen00Author Commented:
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
 
rcowen00Author Commented:
fyed,  Barry's solutions does work for me.  Thanks everyone for your input.
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.