Excel Formula minutes convert to hours

Hi does anyone know the formula for excel so it will add the minutes after
the decimal and convert them to hours.

Need to check my log book times and make a database off information types
flown etc.

EG

1.33
1.56
=2.89

Needs to say 60 mins is one hour

so

1.33
1.56
=3.29

???????????????

=SUM(E1:E23)

=IF(A31000,TIMEVALUE(LEFT(A3,1)&":"&RIGHT(A3,2)),T IMEVALUE(LEFT(A3,2)&":"&RIGHT(A3,2)))
The formatting of the cells in column B is HH:MM.

Thanks Mal

Mal wrote:

Hi does anyone know the formula for excel so it will add the minutes after
the decimal and convert them to hours.

Try a newsgroup dedicated to Excel... The fact that this is about adding up
your logbook does not make it less off-topic.

André

"André Somers" wrote in message
...
Mal wrote:

Hi does anyone know the formula for excel so it will add the minutes after
the decimal and convert them to hours.

Try a newsgroup dedicated to Excel... The fact that this is about adding up
your logbook does not make it less off-topic.

I respect your opinion André, but I must disagree. I have had exactly this
problem for years, and have gotten around it in various (mostly unsatisfactory)
ways. I therefore find the subject very interesting and topical.

If we were talking about painting gliders would you consider that also
something OT to be taken to some paint newsgroup?

Vaughn



André

Mal wrote:
Hi does anyone know the formula for excel so it will add the minutes after
the decimal and convert them to hours.

I found it easier to do hours and minutes in separate Excel columns.

Say J is the hours column and K is minutes for each entry.

Then we sum up total hours, including decimal fractions of hours,
in a hidden L column:

L10=L9+J10+K10/60

(note: L9 was the previous total, before this entry)

Then the total time in hours and minutes is represented in
column M (hours) and N (minutes) respectively:

M10=INTEGER(L10)
N10=L10*60-M10*60

Cheers CV

Why not format the cells to a time format thus-
Format - cells - time - 37:30:55

This will give time in hours minutes and seconds that can be added

"OscarCVox" wrote in message
...
Why not format the cells to a time format thus-
Format - cells - time - 37:30:55

This will give time in hours minutes and seconds that can be added

Sorry excell dont work like that. If you try to enter say 1hr 30min as 1.5
it trys to use it as a date function and displays 12:00:00 and if you try to
add times it treats them also as dates such that 12:00:00 + 12:00:00 =
0:00:00.

I use two columns and add one mod 60 etc.

Peter.

sorry... excel does work like that... you have to format the time..

and don't use decimal to denote minutes.. 1.30 is not 1hr 30 min, 1.5 is
one hour thirty minutes.. or format the cell to TIME, and 1hr 30 minutes is
1:30

BT

"Peter Seddon" wrote in message
...

"OscarCVox" wrote in message
...
Why not format the cells to a time format thus-
Format - cells - time - 37:30:55

This will give time in hours minutes and seconds that can be added

Sorry excell dont work like that. If you try to enter say 1hr 30min as 1.5
it trys to use it as a date function and displays 12:00:00 and if you try
to add times it treats them also as dates such that 12:00:00 + 12:00:00 =
0:00:00.

I use two columns and add one mod 60 etc.

Peter.

Why not format the cells to a time format thus-
Format - cells - time - 37:30:55

This will give time in hours minutes and seconds that can be added

Sorry excell dont work like that. If you try to enter say 1hr 30min as 1.5
it trys to use it as a date function and displays 12:00:00 and if you try to
add times it treats them also as dates such that 12:00:00 + 12:00:00 =
0:00:00.

I use two columns and add one mod 60 etc.

Peter.



Excell does work like that! ie for a flight of 8hrs 57min you put 8:57
You have to use the format including seconds at the one for just hrs:min will
give you a false reading at higher values.
I have entered my logbook for over 600 flights using this method and it works
for me.

"OscarCVox" wrote in message
...
Why not format the cells to a time format thus-
Format - cells - time - 37:30:55

This will give time in hours minutes and seconds that can be added

Sorry excell dont work like that. If you try to enter say 1hr 30min as 1.5
it trys to use it as a date function and displays 12:00:00 and if you try
to
add times it treats them also as dates such that 12:00:00 + 12:00:00 =
0:00:00.

I use two columns and add one mod 60 etc.

Peter.



Excell does work like that! ie for a flight of 8hrs 57min you put 8:57
You have to use the format including seconds at the one for just hrs:min
will
give you a false reading at higher values.
I have entered my logbook for over 600 flights using this method and it
works
for me.


OK try summing a few time that when added are greater than 24 hrs. Or put a
time in of 45hrs 36mins and see what it does then.

Peter

Go to the menu item Format Cells

Select the Custom Category (at the bottom of the menu
on the left)

Type the following into the 'Type' box in the upper
right of the dialog:

[h]:mm;@

This will give you the standard flight log format -
hhh:mm (no seconds)

This works in Excel 2003 - but I don't think the formats
have changed much if at all since Excel '97

9B



At 19:00 13 December 2004, Marc Ramsey wrote:
Wayne Paul wrote:
Using the format Category of 'Time' and the Type '37:20:55'
I entered 12:22,
14:38, 72:14 and the above mentioned 45:36. Summing
this column gave an
accurate total of 144:50:00.

What version of Excel are you using?

I tried this in Excel 97 (I'm too cheap to upgrade).
The key seems to
be using the '37:30:55' time format in the Format Cells
dialog. If any
other time format is used, the values are interpreted
as time of day,
and wrap around at 24 hours.

Marc