[SOLVED] How to add a column of times

[Bbud]

I have a speadsheet of volunteer times in hr:min format. (1:30, 2:45, 0:30, etc). I'd like to know the syntax for adding a column of numbers, as =SUM(F5:F45) doesnt' work.

thx

 

[rgd1101]

https://support.microsoft.com/en-us/office/add-or-subtract-time-16aa6697-6d6e-49c1-8e2c-3398a7cad6ad

 

[kanewolf]

I have a speadsheet of volunteer times in hr:min format. (1:30, 2:45, 0:30, etc). I'd like to know the syntax for adding a column of numbers, as =SUM(F5:F45) doesnt' work.

thx

First you need to have those times in cells that are time format. Your sum may not work because they are text.
If you put your cursor in one of the cell that has "1:30" does the formula bar show "01:30:00 AM" ? That shows that Excel recognizes it as a time rather than text.

 

[USAFRet]

Format the cells as Time.

It works.

1:20​
2:30​
4:50​
8:40​	=SUM(E1:E6)

 

[Bbud]

I have a column of 50+ numbers, all formatted as Time. They will exceed 24 hours. 2:45, :30, 1:15, etc

In the last cell, I'd like the total.

I've tried =SUM(F5:F54) but that doesn't work.

Could there be something hidden in the cells that is causing problems?

 

[kanewolf]

I have a column of 50+ numbers, all formatted as Time. They will exceed 24 hours. 2:45, :30, 1:15, etc

In the last cell, I'd like the total.

I've tried =SUM(F5:F54) but that doesn't work.

Could there be something hidden in the cells that is causing problems?

Use sum(f3:f54)*24 and be sure that cell is formatted as a number. If it is formatted as a number, by default, you get number of days. So multiplying by 24 gets you total hours.
I tested with 29 rows of 6:16 and got 181.73 hours.
Do you NEED hours and min or is decimal hours acceptable ?

 

[Bbud]

Use sum(f3:f54)*24 and be sure that cell is formatted as a number. If it is formatted as a number, by default, you get number of days. So multiplying by 24 gets you total hours.
I tested with 29 rows of 6:16 and got 181.73 hours.
Do you NEED hours and min or is decimal hours acceptable ?

thx. Need hours/min.

I assume you're referring to just the last cell being formatted as number, correct? All others are formatted as time?

 

[Bbud]

thx. Need hours/min.

I assume you're referring to just the last cell being formatted as number, correct? All others are formatted as time?

How can I send you a link to my Sheets spreadsheet?

 

[USAFRet]

I have a column of 50+ numbers, all formatted as Time. They will exceed 24 hours. 2:45, :30, 1:15, etc

In the last cell, I'd like the total.

I've tried =SUM(F5:F54) but that doesn't work.

Could there be something hidden in the cells that is causing problems?

Try this:
https://webapps.stackexchange.com/q...tion-result-from-hoursminutes-into-just-minut

 

[kanewolf]

thx. Need hours/min.

I assume you're referring to just the last cell being formatted as number, correct? All others are formatted as time?

Yes

What I did is put the =SUM(F5:F54)*24 in F55
and in F56 I put =INT(F55)&":"&ROUND((F55-INT(F55))*60,0)
Which formats the sum in F55 into hours and min format.