Date/Time Calculation for WeekEnds/After Business Hours

[Awk76]

Hello,

What calculation would I enter in a results cell if I wanted to find the delta between 2 times in date format that repersent just the business hours of 8am-5pm, therefore excluding after hours and weekends.

eg.


Date 1 Date 2 Result Time
23/07/07 8:00 24/07/07 14:55 15:55

So far I can't get the caluclation that will compensate for the after hours and week ends.

Thanks.

 

[ADAMC]

This might help,

you can download a sample workbook or adapt the formulas for yourself.
http://www.cpearson.com/excel/DateTimeWS.htm

 

[Awk76]

Thanks for the quick response. Unfortunatly I'm not an Excel power user. This link looks good but doesn't seem to fit exactly what I need and I'm unsure how to modify it to help myself out. I calculates the time in hours...I need minutes and changeing the cell format dosen't help. Also, the formla makes reference to a number of variable but doesn't show where they are stored.

Is there an easier way to accomplish what I'm trying to do?

Thanks for your help.

 

[Awk76]

Here my question better explained:

I need to be able to calculate the amount of time in hours:minutes between one date/time and another. I can subtract one from the other and format the output but I need to also subtract weekend days and evenings (a 8-5 workday).

I can get the net working days (networkdays) but that's not what I want. Is there an easier way than writing a custom function? If not, please provide general pseudocode for the algorithm. Thank you.

Example: If the start value is 12/17/2004 16:00 and the end value is 12/21/2004 10:20 the result should be 12:20.

 

[ADAMC]

Just to clarify, can you confirm you want to count the hours between 2 dates and times exclude hours outside of working hours and exclude weekends?

If that is correct try this:
A1 start date and time
B2 End date and time
C1 start of your working day
D1 end of your working day

Paste code into E1:
=IF(NETWORKDAYS(A1,B1)=1,(MIN(D1,MOD(B1,1))-MAX(C1,MOD(A1,1))),(NETWORKDAYS(A1,B1)-2)*(600/1440)+(D1-MAX(MOD($A$1,1),C1))+MIN(MOD($B$1,1),D1)-C1)

 

[ADAMC]

Heres a result based on the example you provided:

Book1
	A	B	C	D
1	23/07/200708:00	24/07/200714:55	08:00	17:00
2
3
4
5
6				15:55:00
Sheet1

It returns same result you provided, hope it helps.
For the start and finish times you could hide column C and D or you could change the formula so these start times go anywhere for example change c and D in the formula to X and Y.

 

[SRGsMom]

Try looking at this post. It's one I started and got a lot of feedback on.

http://www.mrexcel.com/board2/viewtopic.php?t=283601&postdays=0&postorder=asc&&start=0

 

[barry houdini]

Using my suggestion from that thread.....

with start time/date in A2, end time date in B2, weekday start time (e.g. 8 AM) in E2 and weekday end time (e.g. 5 PM) in E3

=(NETWORKDAYS(A2,B2)-1)*(E$3-E$2)+MOD(B2,1)-MOD(A2,1)

format result cell as [h]:mm

This will give you the correct result assuming your start and end times are always within business hours, will that always be the case?

 

[SRGsMom]

That wasn't always the case for me...and when I tried that, it worked accurately in all of my examples except one.

I used this formula:
=(NETWORKDAYS(B13,D13)-1)*("17:00"-"08:30")+MOD(D13,1)-MEDIAN(NETWORKDAYS(B13,B13)*MOD(B13,1),"08:30"+0,"17:00"+0)

b13 was time the voicemail was left, d13 was time we checked the voicemail

b13 - 7/10/07 8am
d13 - 7/13/07 1pm
results from that formula was 6

results should have been something that was equlivent to 3 days and 5 hours.

I think between the formulas you gave me here and some manual calculations, we have made our time crunch and gotten some accurate data to be able to chart. Again---everyone's help is much apprecaited and I love this board!!

 

[barry houdini]

Rita,

The formula I posted above,

=(NETWORKDAYS(A2,B2)-1)*(E$3-E$2)+MOD(B2,1)-MOD(A2,1)

will give the correct results in the circumstances I described, i.e. where start and end times are within business hours.

In your case the “start time”, i.e. the time the message was received, could be at any time so I provided the longer formula that you quote above (which will cater for start time outside business hours but end time must be within business hours)

In the example you give the start time is outside business hours, i.e. it is before 08:30 so the shorter formula won’t give the correct answer.

The longer formula will give the correct answer in hours and minutes which is 30:00 (3 x days at 8.5 hrs + 4.5 hrs on the last day). If you see 6:00 that is because you don’t have the result cell correctly formatted as [h]:mm.

The formula could be adapted to show the result in the form “3 days 4.5 hours” but that would require a more complex formula and the result will be a text string that you can’t use in further calculations.