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.

 

[SRGsMom]

Awesome...I did have it formatted wrong. Great job! Thanks a million!

 

[skoonce]

OK, I have a question, and I am not an expert excel user so I am a little confused. I am trying to calculate time between the hours of 8:00 am and 12:00 AM. I have the cells set up as 6/1/2010 22:00 in one column and 6/2/2010 15:00 in another column. How do I do the calculation? We do work weekends.

Thanks for any help on this!

 

[barry houdini]

By 12:00 AM do you mean midnight? In which case for your example I assume you'd expect the result to be 9:00

If you have start time/date in A2 and end time/date in B2 try this formula in C2

=(INT(B2)-INT(A2))*2/3+MAX(MOD(B2,1),1/3)-MAX(MOD(A2,1),1/3)

custom format C2 as [h]:mm - note the square brackets

 

[skoonce]

i think that worked, thank you.

one more question: when i drag the cells down, it coppies the same info as the first cell. the only way i see it calcualtes the correct formula is when i click on the formula and hit enter, only then will it go to the correct calculation

 

[barry houdini]

That sounds like you have Calculation set to Manual - press F9 to re-calculate. Change to automatic calculation like this in Excel 2003 or earlier

Tools > Options > Calculation > select "Automatic"

or in 2007/2010 choose "Formulas" tab and "Calculation Options" to the right. Set to automatic

 

[skoonce]

Thank you so much! I appreciate all your help.