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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,986
Office Version
  1. 2019
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
79
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,113,903
Messages
5,544,974
Members
410,647
Latest member
LegenDSlayeR
Top