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