» Converting Time to Decimal Values

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

To conver time to decimal simply multiply it by 24 formatting the result to two places of decimal.

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
The answer should be -63.00

How should the formula be modified to accomadate "negative" time?

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 !

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

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?

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.

I am trying to get excel to subtract 2:30 from 4:38 to get and answer of 1.2 hours

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.

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.

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

Does that solve your problem?

Alan.

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?

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.