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"

To convert time to decimal values:

you can use below formula

=HOUR(A3)+(MINUTE(A3)/60)

or if you want to convert Minute value you can use formula called "CONVERT"

=HOUR(A3) + CONVERT(MINUTE(A3),"mn","hr")

Time Hour Decimal

Comments

  1. 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 (https://www.exceltip.com/tips/round-a-number-in-microsoft-excel.html) your requirement will fulfil with the proper tutorail.

      Thanks
      Site Admin

  2. 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?

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

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

  5. "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."

  6. 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?

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

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

  9. "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."

  10. "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."

  11. 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?

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

  13. 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 !

  14. "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? "

  15. "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."

  16. 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?

  17. "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."

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

  19. "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."

  20. "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."

  21. 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?

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

  23. 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 !

  24. "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? "

Leave a Reply

Your email address will not be published. Required fields are marked *

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.