Summing Time

[JDT]

Hi,

I have a column with an amount of time entered. For ex:

1:38
2:05
3:00
0:45
6:27

etc.

When I try summing these time increments in another cell, the answer
is 0:00 if the total is over 24. I just need to be able to sum the
column and see how much total time there is--hours and minutes. I
know it's a simple solution.

Thanks.

 

[Nick Hodge]

JDT

Format the cells to [hh]:mm through Format>cells...>number>custom

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[JDT]

DT

Format the cells to [hh]:mm through Format>cells...>number>custom

I tried formatting using the Format>Cells>Time hh:mm and I also tried
Format>Custom> h:mm and neither one worked.

Let's say in A1 I input the time in and in A2 I input the time out.
The amount of time worked will be A2-A1 in A3. Now, in A4 I want the
cumulative total of A3 as I add data down the list. The way I "was"
doing it worked fine until the total exceeded 24 and then it went to
0:00.

Either way I try it all I come up with is zeros.

Thanks.

JDT

 

[Nick Hodge]

JDT

Me thinks you didn't read the reply ;-)

Format>Cells...>Custom and enter [hh]:mm

Note the [ ]. It stops the hours incrementing to days over 24, likewise
[mm]:ss will stop minutes incrementing to hours over 60

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

DT

Format the cells to [hh]:mm through Format>cells...>number>custom

I tried formatting using the Format>Cells>Time hh:mm and I also tried
Format>Custom> h:mm and neither one worked.

Let's say in A1 I input the time in and in A2 I input the time out.
The amount of time worked will be A2-A1 in A3. Now, in A4 I want the
cumulative total of A3 as I add data down the list. The way I "was"
doing it worked fine until the total exceeded 24 and then it went to
0:00.

Either way I try it all I come up with is zeros.

Thanks.

JDT

 

[JDT]

Me thinks you didn't read the reply ;-)

Format>Cells...>Custom and enter [hh]:mm

Note the [ ]. It stops the hours incrementing to days over 24, likewise
[mm]:ss will stop minutes incrementing to hours over 60

OK, I found it. Format>Cells>Time 37:30:55 also works. However, I
don't have [hh]:mm, but I do have [h]:mm:ss, which works. Am I
overlooking something, or is that an add on or something? (Office
2000)

Thanks for your help

JDT

 

[Nick Hodge]

JDT

No...go to the menu option Format

Then Cells...

The Number Tab

The bottom of the list is an entry called Custom

Click on this

A list with a box at the top will appear

In the box type [hh]:mm or [h]:mm

The difference is with 1:00 hour the first one will show 01:00 the second
1:00

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

Me thinks you didn't read the reply ;-)

Format>Cells...>Custom and enter [hh]:mm

Note the [ ]. It stops the hours incrementing to days over 24, likewise
[mm]:ss will stop minutes incrementing to hours over 60

OK, I found it. Format>Cells>Time 37:30:55 also works. However, I
don't have [hh]:mm, but I do have [h]:mm:ss, which works. Am I
overlooking something, or is that an add on or something? (Office
2000)

Thanks for your help

JDT

 

[JDT]

JDT

No...go to the menu option Format

Then Cells...

The Number Tab

The bottom of the list is an entry called Custom

Click on this

A list with a box at the top will appear

In the box type [hh]:mm or [h]:mm

The difference is with 1:00 hour the first one will show 01:00 the second
1:00

OK, thanks. I wasn't typing anything in; I was just looking for the
drop down box. I didn't know you could type-in. Thanks again.

 

[Guest]

I also cannot get Excel to sum time. I used the formula [h]:mm but it did
not work -the number is still displayed as 1:05 AM (for example). I'm not a
whiz at Excel so need more help.

Thanks,

Patti

JDT

Format the cells to [hh]:mm through Format>cells...>number>custom

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

Hi,

I have a column with an amount of time entered. For ex:

1:38
2:05
3:00
0:45
6:27

etc.

When I try summing these time increments in another cell, the answer
is 0:00 if the total is over 24. I just need to be able to sum the
column and see how much total time there is--hours and minutes. I
know it's a simple solution.

Thanks.

 

[Dave Peterson]

Maybe the stuff you're trying to add up isn't really time--maybe it's text
masquerading as time.

If one of your cells is A1, try putting this in an empty cell:

=isnumber(a1)

If this evaluates as False, then your times aren't times!

Maybe you can select the range of offending cells and
edit|replace
what: : (just the colon)
with: : (just another colon)
replace all

Maybe excel will reevalute those cells and see that they're really times.

I also cannot get Excel to sum time. I used the formula [h]:mm but it did
not work -the number is still displayed as 1:05 AM (for example). I'm not a
whiz at Excel so need more help.

Thanks,

Patti

JDT

Format the cells to [hh]:mm through Format>cells...>number>custom

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

Hi,

I have a column with an amount of time entered. For ex:

1:38
2:05
3:00
0:45
6:27

etc.

When I try summing these time increments in another cell, the answer
is 0:00 if the total is over 24. I just need to be able to sum the
column and see how much total time there is--hours and minutes. I
know it's a simple solution.

Thanks.