Excel Formula minutes convert to hours

[Mal]

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)),TIMEVALUE(LEFT(A3,2)&":"&RIGHT(A3,2)))
The formatting of the cells in column B is HH:MM.

Thanks Mal

[André Somers]

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é

[CV]

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

[OscarCVox]

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

[Keith W]

"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

[Peter Seddon]

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

[Vaughn]

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

[BTIZ]

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)),TIMEVALUE(LEFT(A3,2)&":"&RIGHT(A3,2)))
> The formatting of the cells in column B is HH:MM.
>
> Thanks Mal
>
>
>

[BTIZ]

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

[CV]

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

[Chris Kaminski]

At 12:00 12 December 2004, Mal wrote:
>Hi does anyone know the formula for excel so it will
>add the minutes after the decimal and convert them
to hours.

format the cells to h:mm
you can also format to hh:mm
or even to hh:mm:ss
when adding two cells is likely to lead to hours in
excess of 9
format [hh]:mm otherwise the sum will go wrong

enter the times as 1:27 (1 hour 27 min)
best rgrds Chris

[CV]

Chris Kaminski wrote:
> At 12:00 12 December 2004, Mal wrote:
>
>>Hi does anyone know the formula for excel so it will
>>add the minutes after the decimal and convert them
>
> to hours.
>
> format the cells to h:mm
> you can also format to hh:mm
> or even to hh:mm:ss
> when adding two cells is likely to lead to hours in
> excess of 9
> format [hh]:mm otherwise the sum will go wrong
>
> enter the times as 1:27 (1 hour 27 min)
> best rgrds Chris

What is VERY annoying with that solution is that you
cannot enter flights less than one hour by simply
writing the number of minutes.

Eg. for a 23 minute flight you actually _have_ to
write 0:27 which means you have to press four keys
instead of two.

CV

[CV]

CV wrote:
> Eg. for a 23 minute flight you actually _have_ to
> write 0:27 which means you have to press four keys
oops, I meant 0:23 of course

> instead of two.

CV

[Keith W]

> > 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.
>
Yes - but I was putting over the principal. I assume that the list of times
is in (say) column A. Set dummy columns B and C to carry the split times -
copy the formula down as far as necessay, add and revert back concatenated
time to bottom of column A (using the contraction descibed earlier).

Keith

[Vaughn]

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

One way is to put hours in one cell and minutes in the other and then
convert all to minutes in a non-displayed cell [(hours X 60) + minutes]. At the
bottom of the page you add together all of the minutes and then convert back to
hours and minutes by using the interger (INT) function. To get the hours, use
the formula =hours. To recover the fraction of an hour in
minutes, multiply the hours (from the previous formula) times 60 and then
subtract from the minutes.

Clear as mud?

Vaughn
[i]
>
> 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)),TIMEVALUE(LEFT(A3,2)&":"&RIGHT(
A3,2)))
> The formatting of the cells in column B is HH:MM.
>
> Thanks Mal
>
>
>

[OscarCVox]

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

[Peter Seddon]

"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

[Wayne Paul]

"Peter Seddon" > wrote in message >
> 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
>

Peter,

I have been watching this thread and thought I would see what happens on my
computer using Excel as delivered in Office 2000.

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?

Wayne,
http://www.soaridaho.com/

[Marc Ramsey]

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

[Peter Seddon]

"Marc Ramsey" > wrote in message
. com...
> 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

I'm on excel 2003 and the 37:30:55 time format aint available to me.

Peter.

[Peter Seddon]

"Wayne Paul" > wrote in message
...
>
> "Peter Seddon" > wrote in message >
>> 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
>>
>
> Peter,
>
> I have been watching this thread and thought I would see what happens on
> my
> computer using Excel as delivered in Office 2000.
>
> 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?
>
> Wayne,
> http://www.soaridaho.com/
>
>

I'm on 2003 but the format you selected is for mins and sec + one dec place
of secs not hrs and mins

Peter.

[Andy Blackburn]

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
>

[Marc Ramsey]

Peter Seddon wrote:
> "Marc Ramsey" > wrote in message
>>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
>
> I'm on excel 2003 and the 37:30:55 time format aint available to me.

It is 99.9% likely that there is an equivalent in Excel 2003, as
Microsoft never takes code out, they just add new features that nobody
uses 8^). There are probably at least three distinct hh:mm:ss entries
in the format dialog, one should work.

Marc

[OscarCVox]

>> 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
>
Entered it, added it, it worked!!!
Read and try it

[Bob K.]

You don't need Excel. Just go sailing on the Great Lakes in November.
They say that the waves turn the minutes to hours...

Bob K.

[Peter Seddon]

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

That one works a treat;-)

Peter.

[tango4]

Using later versions of Excel highlight the column that you will use for
your time entries and then select the format of that column ( Format /
Cells ) as 'Number' and select 'Custom' in the list box. In the 'type' box
enter [hh]:mm ( where 'general' is displayed )

The column will now total properly. ie

14:00
15:00
10:00

=39:00

You have to enter hours as X: ie with the colon otherwise X is interpreted
as X days - this can be picked up visually since the cell will display 24 *
X if you enter just the X.
minutes must be entered as 0:mm ie: with the leading zero otherwise the
entry is simply ignored.

Ian

[Peter Seddon]

"tango4" > wrote in message
...
> Using later versions of Excel highlight the column that you will use for
> your time entries and then select the format of that column ( Format /
> Cells ) as 'Number' and select 'Custom' in the list box. In the 'type'
> box enter [hh]:mm ( where 'general' is displayed )
>
> The column will now total properly. ie
>
> 14:00
> 15:00
> 10:00
>
> =39:00
>
> You have to enter hours as X: ie with the colon otherwise X is interpreted
> as X days - this can be picked up visually since the cell will display 24
> * X if you enter just the X.
> minutes must be entered as 0:mm ie: with the leading zero otherwise the
> entry is simply ignored.
>
> Ian
>
>
>
>

Yes I used that system and now my log book is workin perfectly on excel.

Peter.