Adding up times Also converting to Decimal

  • Thread starter Thread starter Charles
  • Start date Start date
C

Charles

Can anyone help? The formula that is recommended in help
does not work - and why would it....?

MS say that to add up times that come to more than 24
hours just use =SUM(A2:A6)*24. I used to think I was
smart but .... how on earth is this ever going to solve
anything - needless to say it doesn't

My columns are in the Custom hh:mm format. When I need
to sum them it works find until you get to 24 hours then
it is lost. All Microsofts help answer does is
multiplies the answer by 24..... like what did they think
it was going to do...? Any ideas ? (e-mail address removed)

Also I need to convert from hh:mm to decimal (ie 1h
18mins to 1.3 hours. MS help gives =(A3-INT(A3))*24
Number of hours since 12:00 AM (12.25) but int does not
recognise time format so gives a retun of 0:00 Any help
appreciated.
 
Hi Charles!

Use custom format [hh]:mm:ss

It stops the sum of the time from rolling over to a day if greater
than 24.

Re:
Also I need to convert from hh:mm to decimal (ie 1h
18mins to 1.3 hours. MS help gives =(A3-INT(A3))*24
Number of hours since 12:00 AM (12.25) but int does not
recognise time format so gives a retun of 0:00 Any help
appreciated.
Click to expand...

Format the result as General

To convert times to decimal just multiply by 24 but make sure that the
result is formatted as General.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
..
 
No dice :-(

I tried this suggestion but it doesn't quite work. The
first one formatting all the cells to be summed to to the
custom format [h]:mm but summing them in whatever format
only gives you the remainder from 24 hours. Putting it
in General does work but it is then a decimal figure
whereas what I wanted was 43:55 (meaning forty three
hours and fifty five minutes, etc).

I have resorted to using int to place the hours into one
field and in the next cell using the sum *24 then
subtracting the int part of that and multiplying the
result by 60. It is very messy and I can't help thinking
there is another way. Also, I would have preferred
having the whole answer in one cell ie 43:55

am I the only person with this problem.....?
-----Original Message-----
Hi Charles

first use the custom format [h]:mm This will prevent the 'roll-over'
after 24 hours.
for the second part: Try formating the target cell as General or number

Also have a look at
http://www.cpearson.com/excel/datetime.htm
for further information regarding times/dates

HTH
Frank
Can anyone help? The formula that is recommended in help
does not work - and why would it....?

MS say that to add up times that come to more than 24
hours just use =SUM(A2:A6)*24. I used to think I was
smart but .... how on earth is this ever going to solve
anything - needless to say it doesn't

My columns are in the Custom hh:mm format. When I need
to sum them it works find until you get to 24 hours then
it is lost. All Microsofts help answer does is
multiplies the answer by 24..... like what did they think
it was going to do...? Any ideas ? (e-mail address removed)

Also I need to convert from hh:mm to decimal (ie 1h
18mins to 1.3 hours. MS help gives =(A3-INT(A3))*24
Number of hours since 12:00 AM (12.25) but int does not
recognise time format so gives a retun of 0:00 Any help
appreciated.
Click to expand...


.
Click to expand...
 
Hi

sorry for beeing not precise enough:
You have to format the resulting cell with the format [h]:mm not the
cells to be summed up.

HTH
Frank

No dice :-(

I tried this suggestion but it doesn't quite work. The
first one formatting all the cells to be summed to to the
custom format [h]:mm but summing them in whatever format
only gives you the remainder from 24 hours. Putting it
in General does work but it is then a decimal figure
whereas what I wanted was 43:55 (meaning forty three
hours and fifty five minutes, etc).

I have resorted to using int to place the hours into one
field and in the next cell using the sum *24 then
subtracting the int part of that and multiplying the
result by 60. It is very messy and I can't help thinking
there is another way. Also, I would have preferred
having the whole answer in one cell ie 43:55

am I the only person with this problem.....?
-----Original Message-----
Hi Charles

first use the custom format [h]:mm This will prevent the 'roll-over'
after 24 hours.
for the second part: Try formating the target cell as General or
number

Also have a look at
http://www.cpearson.com/excel/datetime.htm
for further information regarding times/dates

HTH
Frank
Can anyone help? The formula that is recommended in help
does not work - and why would it....?

MS say that to add up times that come to more than 24
hours just use =SUM(A2:A6)*24. I used to think I was
smart but .... how on earth is this ever going to solve
anything - needless to say it doesn't

My columns are in the Custom hh:mm format. When I need
to sum them it works find until you get to 24 hours then
it is lost. All Microsofts help answer does is
multiplies the answer by 24..... like what did they think
it was going to do...? Any ideas ? (e-mail address removed)

Also I need to convert from hh:mm to decimal (ie 1h
18mins to 1.3 hours. MS help gives =(A3-INT(A3))*24
Number of hours since 12:00 AM (12.25) but int does not
recognise time format so gives a retun of 0:00 Any help
appreciated.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top