|

# » Converting Time to Decimal Values

To convert time to decimal values:

Use the HOUR and MINUTE formulas to extract the hour and minute numbers from the time.
Screenshot // Converting Time to Decimal Values

Rate This Tip
 1 2 3 4 5
Rating: 2.96     Views: 558896
Converting Hours and Minutes to Decimal
Alan  Posted on: 31-12-1969
This formula is excellent, but how do you convert a time if it shows hours but a decimal figure for Minutes i.e. 20.83 to 20:50
Time to Decimal
Mark  Posted on: 31-12-1969
To conver time to decimal simply multiply it by 24 formatting the result to two places of decimal.
Converting "negative" time to decimals
Natalie  Posted on: 31-12-1969
I'm trying to convert time to decimals in Excel. I've used the following formula for "positive" time:
x =60*(HOUR(R10)+(MINUTE(R10)/60))i.e. R10=1:03:55; x =63.00

This formula does not work for
R10=-1:03:55

How should the formula be modified to accomadate "negative" time?
How About Seconds to Hours, Minutes
Merlot54  Posted on: 31-12-1969
This tip was interesting, but I'm trying to find out how to convert seconds to hours, then minutes in Excel. If in Cell A1 I have 3,900 seconds I can get A2 to relfect minutes by using A1/60 which is 65 minutes, but I cannot get A3 to reflect anything but 1.08 which is 1 point 08 hours, when I want 1 hour, 5 minutes to be displayed. 1:05. Help !
Converting Hours and Minutes to Decimal
Alan  Posted on: 31-12-1969
Okay, that formula works, however, if I exceed 24 hours, the formula will turn 24:30 to 0.30. Now I know that I can format the cell for 24 hours by usin [h]h.mm, but this does not work in this instance. How do I get the formula to show the hours exceeding 24.00
computation of elapse time mulitply by 60 minute
alvin caspe  Posted on: 31-12-1969
im making time computaion for my internet cafe using excel. i cant compute the time total time usage to 60 minute so tha ti can comoute it ot my desired payment per minute. how can i convert the total time usage to a dacimal?
Reply: How About Seconds to Hours, Minutes - Merlot54 from Canada wrote on July 9, 2003 10:26 PM EST
Alan  Posted on: 31-12-1969
Hi Merlot54,

If you have A1 = 3900 (seconds)
Then:

A2 = A1/60 = 65 (minutes)

A3 = (A2/60)/24 = 0.00075 (of a day)

If you now format A3 using:

[hh]:mm:ss

You will get:

00:01:05 (hours, mins, seconds).

The trick is to be very careful with your units. 1.08 on its own, will mean 1.08 days to excel (~25 hours, 55 mins, and 12 seconds).

HTH,

Alan.
Converting time to decimals
Eric Martinez  Posted on: 31-12-1969
I am trying to get excel to subtract 2:30 from 4:38 to get and answer of 1.2 hours
Reply: Converting time to decimals - Eric Martinez from Tulsa, OK wrote on September 3, 2003 3:26 PM EST
Alan  Posted on: 31-12-1969
Hi Eric,

That will be a challenge!

Perhaps you could set out your detailed reasoning / calculation and we can have a look.

In general, to find out the difference between two times, just enter hour:min times in two cells, and subtract one from the other. Obviously that won't give the answer you are looking for above though!

Alan.
Converting times to decimals
Eric Martinez  Posted on: 31-12-1969
I think that ihave figured this out, it seems to work so far. If you subtract one time from the next and then *24 the answer you will get teh answer in decimal tenths of an hour. If you limit the anser to one decimal place it looks right so far. Thanks for your quick response. In aviation time are use in tenths of as hour.
Reply: Converting times to decimals - Eric Martinez from Tulsa, Ok wrote on September 4, 2003 1:35 AM EST
Alan  Posted on: 31-12-1969
Hi Eric,

If you subtract one time from another, and then multiply by 24, you will shift your units from days (Excel's unit of time) to hours.

You have ot be careful at that point, since you are no longer using the 'normal' unit of time - you'll have to be explicit in all your calculations.

If you just want to diaply a time as a decimal, then change the display format from, say, hh:mm to General.

Example:

If you enter 2:38 in a cell it displays as 02:38 being 2 hours and 38 minutes.

If you change the format of the cell to General (say), then you see:

0.109722222222222

That decimal was always the value in the cell - we only changed the format that it was displayed in. It repesents the proportion of a day that is 2 hours and 38 mins.

Alternatively, if you multiply that value by 24, you get:

2.63333333333333

In other words, 2.6.. hours is approximately 2 hours 38 minutes (actually 2 hours 36 minutes but I rounded the value to one decimal place as you indicated above).

Alan.
Time Card Quetion
Bridget Richardson  Posted on: 31-12-1969
I am trying to create a timecard. How do I enter "time in" , "time out", "time in", "time out", and then calculate those times to come up with the total time worked?
Reply: Time Card Quetion - Bridget Richardson from NC wrote on September 4, 2003 11:41 AM EST
Alan  Posted on: 31-12-1969
Hi Bridget,

Just subtract each start time from the associated finish time, and add together the results.

Watch your formatting and data entry.

The standard excel time unit is a day.

If you enter start time of 08:00 it will *display* in that format, but the cell will contain the *value* 0.3333... (= 8 / 24 of a day).

Alan.
Name
Comment Title