itkamisama

asked on

# Adding hours & minutes

I want to add hours and minutes in excel.

However when going over 24, the spreadsheet doesn't know what to do.

Example:

13:30 + 2:45 + 11:15

I tried changing the formatting to Time > 37:30:55 and it gave me 339:30:00

The cells that contain the time are calculated cells, which calculate the difference between two times of the day

The formatting for those cells is Time > 13:30

However when going over 24, the spreadsheet doesn't know what to do.

Example:

13:30 + 2:45 + 11:15

I tried changing the formatting to Time > 37:30:55 and it gave me 339:30:00

The cells that contain the time are calculated cells, which calculate the difference between two times of the day

The formatting for those cells is Time > 13:30

If you add those 3 times and get 339:30 then [at least one of] the individual times must be higher than 13:30, 2:45 and 11:15.

Try formatting everything with the 37:30:55 format (which is essentially the same format as teylyn's suggested [h]:mm)

Are the values what you expect? If not then there is possibly something wrong with the formulas you are using to calculate the time difference. Can you post a sample formula?

regards, barry

Try formatting everything with the 37:30:55 format (which is essentially the same format as teylyn's suggested [h]:mm)

Are the values what you expect? If not then there is possibly something wrong with the formulas you are using to calculate the time difference. Can you post a sample formula?

regards, barry

ASKER

I still can't get it to work

I am enclosing the spreadsheet. Maybe it makes more sense that way.

contractTimes.xlsx

I am enclosing the spreadsheet. Maybe it makes more sense that way.

contractTimes.xlsx

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

You were absolutely correct. Once I change > to < everything worked perfectly fine. Thank you for your help.

you need to format the result with

[hh]:mm

note the brackets! Otherwise, Excel will interpret everything above 24 hrs as a day.

cheers, teylyn