Decimal Hours ?

  • Thread starter Thread starter Phil Jones
  • Start date Start date
P

Phil Jones

I have a project which requires me to show time as Decimal hours, i.e. 08:30
= 08:50 etc.

Does anyone know how I would go about this or is their a setting within
excel to display time as decimal hours?

Thanks in advance

Phil
 
Phil Jones said:
I have a project which requires me to show time as Decimal hours, i.e. 08:30
= 08:50 etc.

Does anyone know how I would go about this or is their a setting within
excel to display time as decimal hours?
Click to expand...

Since time is internally stored as a number from 0-1, you could multiply
the value by 24, and then format it with "0.00" in the custom formats
dialog box.
 
Get the minutes from the time, and divide them by sixty.

Time is stored as an 8 byte number that represents the
number of seconds since... 1900 I think. If someone knows
for a fact, feel free to chime in.
 
One way is to use a helper cell.

Keep the "real" time and then just put a formula in that helper cell that show
it pretty:

=TEXT(HOUR(A1),"00")&":"&TEXT(100*MINUTE(A1)/60,"00")

Do all your time arithmetic against the real cell (since this formula evaluates
to a text string).
 
XL stores date and time as

Date: an integer offset in days from a base date (12/31/1899 in
the 1900 date system, 1/1/1904 in the 1904 system).

Time: stored as fractional days, e.g 3:00 = 0.125

Thus, in the 1900 date system, 37897 represents 37,897 days after
12/31/1900, or 3 October 2003 (kind of - the date system screwed up
with 29 February 1900).

If there's a fractional part, say 37897.5, the fractional part
represents time, or 3 October 2003 12:00 noon.

Since time is stored as fractional days, to get decimal hours, just
multiply by 24:

9:00 = 0.375 ==> 0.375 * 24 = 9.00
 
Back
Top