x
Solved

# Excel 2013 - Figuring working hours only between two dates

Posted on 2015-02-04
Medium Priority
153 Views
I need help in developing a formula to calculate BUSINESS HOURS ONLY from one cell with a date and time and another cell with a date and time.  Each day’s hours are from 8:00 am to 4:30 pm, Monday through Friday. So if I have 2/9/15 3:00 pm in cell A1 and and 2/10/15 10:30 am in cell A2, I want the calculation to return 4 hours only in cell A3.
0
Question by:esu4236
• 6
• 6

LVL 27

Expert Comment

ID: 40589486
Hi Esu,

see if the attached solution works for you.
EE-TIME.xlsx
0

LVL 6

Expert Comment

ID: 40589496
use time function with networkingdays function.
0

LVL 27

Expert Comment

ID: 40589650
Just put the following formula in A3

=(NETWORKDAYS(A1,A2)-1)*("16:30"-"8:00")+IF(NETWORKDAYS(A2,A2),MEDIAN(MOD(A2,1),"8:00","16:30"),"16:30")-MEDIAN(NETWORKDAYS(A1,A1)*MOD(A1,1),"8:00","16:30")
0

Author Comment

ID: 40589841
ProfessorJimJam:

I used your latest formula. It worked for the sample I gave you, but then when I put in some different dates, it's not always working - seems to have a problem when the span is over 24 hours. I've attached a copy of the file. I modifed the formula to reflect the appropriate cells because the example I initially gave was a simplified version as far as using A1 and A2. Also, is there any way to have the time display in just a number format? So instead of 4:00, can we have it display 4? Or if it's 10 1/2 hours, display that rather than 10:30?
0

LVL 27

Expert Comment

ID: 40589931
all you need to do is to format cell using [h]:mm:ss  format

see attached.
0

Author Comment

ID: 40591475
Okay the formula is working, but I'm assuming there's no way to get it to display 35 1/2 rather than 35:30:00??? That was part of my previous question. If we can't do that, then we'll go with this format. Just thought I'd ask. THANKS!!!!
0

Author Comment

ID: 40591482
I think I just answered my own question regarding the format. I simply took the result of the formula and multiplied it by 24.
0

Author Comment

ID: 40591487
Actually you format the cell for a number, and multiply the result of the formula by 24. That seems to work - still testing to make sure.
0

LVL 27

Expert Comment

ID: 40591494
yes. smart move.  you got it right.  i am glad it worked for you.
0

LVL 27

Accepted Solution

ProfessorJimJam earned 2000 total points
ID: 40591508
if you want it without helper cell then put the formula like this

=((NETWORKDAYS(E8,E10)-1)*("16:30"-"8:00")+IF(NETWORKDAYS(E8,E8),MEDIAN(MOD(E10,1),"8:00","16:30"),"16:30")-MEDIAN(NETWORKDAYS(E8,E8)*MOD(E8,1),"8:00","16:30"))*24
0

Author Comment

ID: 40592915
That's actually how I did do it. So we're on the same page.  :)
0

Author Closing Comment

ID: 40592920
Thanks so much for your help. Seems to be working great!!!!!
0

LVL 27

Expert Comment

ID: 40593274
you are welcome.  thanks for the feedback
0

## Featured Post

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.