cindyfiller
asked on
Need to determine the # of hours between a start and end time AND be able to add them up...
I am trying to find out how many hours certain rooms are used over a period of time. I can accomplish part of this problem, but can't figure out how to get the final result. I have a start time and an end time. I can come up with the number of hours the room was used - such as 2 1/2 hours or 5 hours, etc. But the format for that calculate is a time format. When I try to add up the number of hours (2 1/2 hours + 5 hours should be 7 1/2 hours) it doesn't work. The result is some odd number.
I've tried to copy just the value of that cell but instead of 2 hours it comes up with something like .853245. How can I calculate the number of hours used so that I can add up the totals and get the right number of hours?
I've tried to copy just the value of that cell but instead of 2 hours it comes up with something like .853245. How can I calculate the number of hours used so that I can add up the totals and get the right number of hours?
Can you give a sample workbook...
ASKER
I can display the value ok - but I can't add the value up and get a total that is correct. I'm thinking I need to take the 2:30 and change it to a numeric field so I can add it. Does that make sense?
When I get back to work I'll send a spreadsheet example.
When I get back to work I'll send a spreadsheet example.
You can add the times as they are. Use the SUM function. Then format the result using a custom format:
[h]:mm
Kevin
[h]:mm
Kevin
ASKER
I've attached a small sample of what I'm trying to do. As you can see the totals don't come out right. Hopefully this sample will help. (I did format the total as you recommended but it didn't change the total.)
sample-time-calculation.xlsx
sample-time-calculation.xlsx
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Thank you - I don't know how I missed that. That definitely worked!!
Now I have 2 additional issues. I could only grab the time from the database I'm using... I have a few that have a time of midnight and those are not calculating correctly. And I have a few that went from like 6 pm until 1:30 am the next morning. Any way to fix those? If not I'll manually enter a value - this has been super helpl
Now I have 2 additional issues. I could only grab the time from the database I'm using... I have a few that have a time of midnight and those are not calculating correctly. And I have a few that went from like 6 pm until 1:30 am the next morning. Any way to fix those? If not I'll manually enter a value - this has been super helpl
To handle midnight crossovers, use this formula:
=MOD((C2-B2),1)
Kevin
=MOD((C2-B2),1)
Kevin
ASKER
Absolutely perfect! I wish I could award you more than 500 points!
ASKER
Super responses!
=A1/24
To view the result as total hours and minutes, format the result using the custom format:
[h]:mm
To view the result as total hours, minutes, and seconds:
[h]:mm:ss
Kevin