Solved

Adding hours & minutes

Posted on 2010-11-08
6
3,730 Views
Last Modified: 2012-06-21
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
Comment
Question by:itkamisama
  • 3
  • 2
6 Comments
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
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
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
See attached. Cell A1 has custom format [hh]:mm

cheers, teylyn
Book2.xls
0
 
LVL 50

Expert Comment

by:barry houdini
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 1

Author Comment

by:itkamisama
Comment Utility
I still can't get it to work
I am enclosing the spreadsheet. Maybe it makes more sense that way.
contractTimes.xlsx
0
 
LVL 50

Accepted Solution

by:
teylyn earned 500 total points
Comment Utility
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
 
LVL 1

Author Closing Comment

by:itkamisama
Comment Utility
You were absolutely correct. Once I change > to < everything worked perfectly fine. Thank you for your help.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now