Excel 2013 - Figuring working hours only between two dates

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.
esu4236Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
ProfessorJimJamConnect With a Mentor Commented:
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
 
ProfessorJimJamCommented:
Hi Esu,


see if the attached solution works for you.
EE-TIME.xlsx
0
 
FloraCommented:
use time function with networkingdays function.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
ProfessorJimJamCommented:
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
 
esu4236Author Commented:
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?
Brian-L---Business-Hours-Calculator.xlsx
0
 
ProfessorJimJamCommented:
all you need to do is to format cell using [h]:mm:ss  format

see attached.
Brian-L---Business-Hours-Calculator.xlsx
0
 
esu4236Author Commented:
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
 
esu4236Author Commented:
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
 
esu4236Author Commented:
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
 
ProfessorJimJamCommented:
yes. smart move.  you got it right.  i am glad it worked for you.
0
 
esu4236Author Commented:
That's actually how I did do it. So we're on the same page.  :)
0
 
esu4236Author Commented:
Thanks so much for your help. Seems to be working great!!!!!
0
 
ProfessorJimJamCommented:
you are welcome.  thanks for the feedback
0
All Courses

From novice to tech pro — start learning today.