Converting Time to Decimal Values

This article will convert current local time in (UTC/GMT) – World Time Zone

Use the **HOUR** and **MINUTE** formulas to extract the hour and minute numbers from the time.

For example ..

You have time as “3:30 PM” and you want to converting time to decimals, so that it will display 3o minute as a Half of hour, or display the result as “15:50″

you can use below formula

or if you want to convert Minute value you can use formula called “**CONVERT**”

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

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

Hi Marcel,

For hours such as 25:30 and to convert to 25.50, use:

=DAY(A1)*24+HOUR(A1)+MINUTE(A1)/60

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

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

How to convert 39, 38, 1, 6, 9, 2013 to 9/6/2013 1:38:39 AM by using excel function?

are all (numbers)they in other cells ?

hi room

I am a very basic user and trying to make a sheet for wages I calculate the hours worked say 3 then another 7 I want to show this as each hour equals 1 i.e. 10 then times the answer by 18.62 as currency the result should be £180.62 your help would be much appreciated please remember I am a basic user

I’m an HR professional, and I’m in the process of assigning Bonus componenets.

Eg. for 10 points I’m assigning .55 cents

then 11 points get how many cents.

Hi Karthik,

Kindly post your query @ http://www.excelforum.com

If 1day is equal to 8hrs then if I have several days to compute 5/6 is 1day; 11/7 is 1/2day; 12/10 is 1day so total is 2 1/2day (2 x 8 = 16 + 4 for 1/2) leave subtract to total number of entitled leave e.g. is 7days2hrs how to calculate the balance?

Hi Chona,

Please post your query @ http://www.excelforum.com. You will get the appropriate reply immediately.

I want to calculate the total salary of employees.

When total salary = 22503 then i want to show in cell 22505 and salary = 22502 then i want to show in cell 22500.

Pleas send the formula name and example also

Hello Abdul,

Welcome on Excel Tip!!!

Please go on this mentioned (http://www.exceltip.com/tips/round-a-number-in-microsoft-excel.html) your requirement will fulfil with the proper tutorail.

Thanks

Site Admin

Good day!

Anyone here can give me a vba code on how to convert 65 to 01:05. Your reply is highly appreciated.thanks

My formula:

=CEILING((HOUR((E4-C4)) + MINUTE((E4-C4)) / 60 + SECOND((E4-C4)) / 3600),0.1)

When C4 is the Start Time and E4 is the End Time.

Hi I am Najam

53:20:00 in a1 cell. How can I only convert 20 minutes in cell a1 to a decimal number in excel

For example 53:30:00 in 53.5