Adding column of hours and minutes

cjamps

New Member
Joined
Nov 24, 2017
Messages
12
I have a column of a long list of hours an minutes 00:01:00, 00:15:00, 01:16:00, etc. No number is more than 24 hours. I am trying to add the column to see how many hours and minutes are being used. When I highlight the column and do autosum, I get =autosum(number1, number2, etc) in the topmost cell. if I press the checkmark to run the formula I get an error There's a problem with this formula, not trying to type a formula, etc, etc. Tried formatting column as hh:mm, or as a number. Tried formatting int(a1*24) kept getting 0.

Please advise.
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,601
Office Version
  1. 365
Platform
  1. Windows
Select the range then press Alt = and it should enter the correct formula for you.

If that doesn't give you a result then it would suggest that you data is not in valid time format.
 

ER_Neha

Board Regular
Joined
Jul 25, 2020
Messages
100
Office Version
  1. 365
Platform
  1. Windows
Hello, Please try this again. Also if the sum doesn't give you the correct answer, that might be because the hour is greater than 24 hours, so in that case, you can do the custom formating.
Book1
A
1date
200:01:00
300:15:00
400:20:00
503:01:00
604:01:00
705:01:00
806:01:00
907:01:00
1008:01:00
1109:01:00
12
1342:43:00
Sheet1
Cell Formulas
RangeFormula
A13A13=SUM(A2:A11)
 

cjamps

New Member
Joined
Nov 24, 2017
Messages
12
Yay, Yay, I formatted the column to [h]:mm:ss. I put =sum(a1:a2852) like you suggested and the result in column a:2853 is 0:00:00. I am so excited to be getting somewhere. Clearly there seems to be a problem with the formatting. If I type in manually 0:01:00, 0:15:00, 12:25:00 I would rightly so get 12:41:00. The only thing I can think of is the leading zero. The call log list is in 09:99:00 format. If I manually type it in, excel truncates the first 0. Could that be the issue?
 

Watch MrExcel Video

Forum statistics

Threads
1,118,800
Messages
5,574,404
Members
412,590
Latest member
Velly
Top