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é

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

"CV" 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.

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
At the risk of being too trivial, if the figures are already input, can
split them by:

say hour:mins in A1, put B1=INT(A1) (gives hours in B1), C1=(A1-B1)*100
(gives minutes in C1)

Keith

"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.

"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é

I formatted the cell to "Time" and display hh:mm

Then when you "SUM" the column, the math is correct.

to get the totals to total a large number of hours.. like 310:20 (310 hrs
20 minutes) log book total.. you will need to pick the hh:mm:ss format. the
display will now read 310:20:00

BT

"Mal" wrote in message
...
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

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.

Keith W wrote:
At the risk of being too trivial, if the figures are already input, can
split them by:

say hour:mins in A1, put B1=INT(A1) (gives hours in B1), C1=(A1-B1)*100
(gives minutes in C1)

Yep, that would do the job for one single entry.
However it would not allow you to add two or more
entries to make a total.

To keep a running total of your flying time
you need something a bit more elaborate.
CV