• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3788
  • Last Modified:

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
0
itkamisama
Asked:
itkamisama
  • 3
  • 2
1 Solution
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

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
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
See attached. Cell A1 has custom format [hh]:mm

cheers, teylyn
Book2.xls
0
 
barry houdiniCommented:
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
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
itkamisamaAuthor Commented:
I still can't get it to work
I am enclosing the spreadsheet. Maybe it makes more sense that way.
contractTimes.xlsx
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

to troubleshoot, change the format of all your time values to [hh]:mm and you'll see what's happening.

You are using a formula to calculate times in columns D and I

=IF(time out >time in, time out +1, time out)- time in

This results in 25:30 hrs for a time in = 11:40am and time out = 4:55 pm on the same day. I'm sure that's not what you want.

Change the formula for the calculated time to

=IF(time out < time in, time out +1, time out)- time in

and your sheet will calculate fine.

cheers, teylyn
0
 
itkamisamaAuthor Commented:
You were absolutely correct. Once I change > to < everything worked perfectly fine. Thank you for your help.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now