I have a column with hours ( B2 3:21, B3 2:56, B4 1:19) etc… I need to add these so that I have the number of hours I worked on a project. In that case 7:36, I can’t find a way for excel to do this.
Could anybody help?
Thanks
Select the cells
Right-click > Format cells
Number > Time
Do not have that option, once i click Number, there’s nothing that says Time.
Plus, i want to have the total time in it’s own cell.
Could you please clarify this?
Thanks
You have to put in hours like 1:23:20 is one hour 23 minutes and 20 seconds
Make your column of times like this then in your additions cell at the end of the column go to custom in format cells and find (h):mm:ss
Few different ways to skin this cat. Hope that one works for you.
Try format: custom then scroll down past all the ###/### looking crap to HH:MM
just tried it using the sum with the format above here is what I got:
4:30
5:20
9:50
looks right
edit sorry didn’t see ironmans post.
OK, it still ain’t working, but I appreciate the effort. 
For clarification purpose here’s what happens:
Now, when I add the cells this is the formula I put =sum(B5:B21) the result I get is 18:31
So, something is wrong right? Please help!! 
In that image I can see Time! 6th one down.
Anyway you need to do something special for the “total” cell (B21).
Right-click > Format cells
Choose Custom and in the box enter:
[h]:mm
What was happening was that Excel rolls over the hours once it gets to 24 and starts at 0 again.
Yeah, I misunderstood what you meant.
[quote]Anyway you need to do something special for the “total” cell (B21).
Right-click > Format cells
Choose Custom and in the box enter:
[h]:mm[/quote]
OK, done that, but it still don’t change my result. Would it help if I email you the file?
If it’s ok with you, PM me your email and I’ll send the file.
Thanks
[quote=“Rik”]In that image I can see Time! 6th one down.
[/quote]
yes, seems to work in time 6th one down or in custom.
As mentioned, it is working on a 24 hour clock so,
… A… B
1 1:30
2 2:30
3 =sum(A1:A2) and format this cell to display as time
would result in 4am
13:30
15:30
would result in 4pm depending how you set up the time display.
Highlight all your times, then:
format cells
Number
Custom
[h]:mm:ss
I just did this and the numbers added up properly.
Using Mac OS and office 2004 for mac.
[quote=“dangerousapple”]Highlight all your times, then:
format cells
Number
Custom
[h]:mm:ss
I just did this and the numbers added up properly.
Using Mac OS and office 2004 for mac.[/quote]
Now, i am really puzzled, i use the same system as yours, mac OS X and Office 2004
The result I get is 18:31 for the total. :s
Edit: Thank you Rik for your help on that. I didn’t format the result cell, that was my mistake.
Thanks to all who posted on this thread as well.
If its over 24 hours, you must add * 24 to the formula.
[quote]For times entered in the standard time format (hours : minutes : seconds)
For this method to work, hours can never exceed 24, minutes can never exceed 60, and seconds can never exceed 60.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
Create a blank workbook or worksheet.
Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
1
2
3
4
5
6
A
Hours worked
1:35
8:30
3:00
4:15
8:00
Formula Description (Result)
=SUM(A2:A5) Total hours worked when the total is less than a day (17:20)
=SUM(A2:A6)*24 Total hours worked when the total is greater than a day (25.33333).
Notes
To view the second formula as a number, select the cell and click Cells on the Format menu. Click the Number tab, and then click General in the Category box.
If the time you are adding contain seconds, format the cell with time format that displays seconds. Select the cell and click Cells on the Format menu. Click the Number tab, and then click Time in the Category box.
Function details
SUM
[/quote]