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.
use time function with networkingdays function.
Just put the following formula in A3
=(NETWORKDAYS(A1,A2)-1)*("
=(NETWORKDAYS(A1,A2)-1)*("
ASKER
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
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
all you need to do is to format cell using [h]:mm:ss format
see attached.
Brian-L---Business-Hours-Calculator.xlsx
see attached.
Brian-L---Business-Hours-Calculator.xlsx
ASKER
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!!!!
ASKER
I think I just answered my own question regarding the format. I simply took the result of the formula and multiplied it by 24.
ASKER
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.
yes. smart move. you got it right. i am glad it worked for you.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That's actually how I did do it. So we're on the same page. :)
ASKER
Thanks so much for your help. Seems to be working great!!!!!
you are welcome. thanks for the feedback
see if the attached solution works for you.
EE-TIME.xlsx