still having issues with converting hours:Min:sec into decimal :-(

[hatbrox]

Hi,

I went through a few examples from this website and others about converting duration (not time!) into decimals.
I'm using Excel for mac version 2011

I start with a new spreadsheet.
in Cell A1, I enter 38:59:00
in cell A2, I want to see 38.98
(I'm not interested in the seconds)
The 1st thing I notice is that in the formula bar, Excel shows for cell A1: 01/01/1900 14:23:00
I checked the format of the cell: A1 and it says: "Custom" and then "[h]:mm:ss"
A2 is formatted as: Number with 2 decimals

In A2, I tried the following formula and I get these results (after the "==>")
HOUR(A1)+MINUTE(A1)/60 ==> 14.98
24*MOD(A1,1) ==> 14.98
(HOUR(A1)*60)+MINUTE(A1)+(SECOND(A1)/60) ==> 899.00
(A1-INT(A1))*24 ==> 14.98

basically, Excel hides the First 24 hours, or the 1st day and displays the reminder: 14.98.
Indeed, 24+14.98 = 38.98

the above formulas work fine if the duration is less than 24 hours but not when above.

I want to see 38.98

thanks for suggestions.

 

[Andrew Poulsom]

To convert the serial time in A1 to decimal hours:

=A1*24

formatted as General.

 

[hatbrox]

To convert the serial time in A1 to decimal hours:

=A1*24

formatted as General.

thanks, didn't expect to be that easy!