Hour(A1) if A1 is greater than 24?

  • Thread starter Thread starter Don Culp
  • Start date Start date
D

Don Culp

I have an equation "Hour(A1)+Minute(A1)/60" where I want to convert the time
(A1) into its decimal equivalent. (A1 is formatted as a number of type
Time.) However, this only works as long as A1 is less than 24:00. Otherwise,
Hour(A1) starts subtracting 24 from the actual horns. For example,
Hour(30:00) returns 6 rather than 30. Is there an alternate function that
will do this?

Excel 97

Thanks,
Don Culp
 
Since XL stores times as fractional days, you need only multiply by 24:

=A1*24

and format as General or another number format.
 
A1*24 works for the result but how can I get A1 itself to preserve the
entered time --> if I type 30:10 into A1 (where A1 is formatted as a number
of type Time), A1 then displays 6:10 instead of 30:10. I know that I could
manually calculate 30:15 as 30.17 (formatted as a decimal number) but I A1
to display as hours:minutes.

Thanks,
Don Culp
 
Format/Cell/Number/Custom [h]:mm

the brackets prevent the display engine from rolling over at 24 hours.
 
Back
Top