Date/Time Calculation for WeekEnds/After Business Hours

Awk76

New Member
Joined
Jul 23, 2007
Messages
3
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.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Awk76

New Member
Joined
Jul 23, 2007
Messages
3
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

New Member
Joined
Jul 23, 2007
Messages
3
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

Well-known Member
Joined
Mar 20, 2007
Messages
1,169
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Mar 20, 2007
Messages
1,169
Office Version
  1. 2013
Platform
  1. Windows
Heres a result based on the example you provided:
Book1
ABCD
123/07/200708:0024/07/200714:5508:0017:00
2
3
4
5
615: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.
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

New Member
Joined
Jul 20, 2007
Messages
17
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!! :biggrin:
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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.
 

Forum statistics

Threads
1,181,399
Messages
5,929,743
Members
436,687
Latest member
Glass of Gin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top