Solved

Excel 2013 - Figuring working hours only between two dates

Posted on 2015-02-04
13
136 Views
Last Modified: 2015-02-06
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
Comment
Question by:esu4236
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
13 Comments
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40589486
Hi Esu,


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

Expert Comment

by:Flora
ID: 40589496
use time function with networkingdays function.
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
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
Easy, flexible multimedia distribution & control

Coming soon!  Ideal for large-scale A/V applications, ATEN's VM3200 Modular Matrix Switch is an all-in-one solution that simplifies video wall integration. Easily customize display layouts to see what you want, how you want it in 4k.

 

Author Comment

by:esu4236
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?
Brian-L---Business-Hours-Calculator.xlsx
0
 
LVL 26

Expert Comment

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

see attached.
Brian-L---Business-Hours-Calculator.xlsx
0
 

Author Comment

by:esu4236
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

by:esu4236
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

by:esu4236
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 26

Expert Comment

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

Accepted Solution

by:
ProfessorJimJam earned 500 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

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

Author Closing Comment

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

Expert Comment

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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article describes how to use the Send to Mail Recipient command. The instructions apply generally to Office 2007 and later versions, but Microsoft® Word 2013 was used for the specific steps and figures.  What is Send to Mail Recipient? Send…
With the internet and the ease of information transference, many professional jobs can be done anywhere today.  Why should it make a difference whether an x-ray is read in India or the United States as long as the radiologist is qualified?   Outso…
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question