Excel 2010: Convert Number Values Into Time

There are many ways in which you can enter time into cell, but Excel has an built-in function which facilitates users to convert values into time format, by using this function you can convert data in hrs:mm format, and hrs:mm:sec format. It automatically shows military time format against the suitable values in the cell. This post covers how to change values into time format.

Launch Excel 2010, open a datasheet in which you want to convert values to time format.

values

We are intending to convert values in the Time (Values) field into hh:mm, now we will be adding another field with a label Time (hrs:mins). We will be writing a simple formula in the first row.

new field 1

Now lets add the formula;

=B2/(24)

In formula, we are dividing time by 24 (24 hrs sums up a day). This will yield value in decimal.

formula result


Now select the cell that contain result, and right-click to select Format Cells.

format cells

Format Cells dialog will appear, from the left pane select Custom, and from the right pane under Type, look for h:mm AM/PM, and click OK.

hours mins 1

Here you can see in the screenshot below, that the value is now converted into Time format.

time 1

Now drag the plus sign at the end of the cell towards the end of the column, to apply it over the whole field.

new 1

You can also check out previously reviewed guides on SmartArt in Excel 2010 & Data Bars in Excel 2010.

Advertisement
  • israel fregoso

    What about minutes to hour:minutes:secs?

    I see your formula converts decimals to secs, then how can I conver 189.25 minutes into 3:09:15 (H:M:S) ?

    thanks!

    • AVercammen

      hh:mm:ss with will have 2 digits for the hrs

  • Diana V

    hey I want to know how to convert a numeric (non-decimal/value) entry to minutes and seconds, for example: in a single entry of numbers 4520 into 45:20, that will be so helpful in a project I’m doing from work, pleaseee

    • Skypenguin

      use a formula in a new column. If your value is in cell a1 then type =left(a1,2)&”:”&right(a1,2) Then drag down the entire row.

      • Scott M

        That doesn’t help. There should be a FORMAT style (not a formula) that automatically converts a numeric entry in a formatted cell into a TIME.

        For example: In Cell A1, enter “800″.

        If A1 is formatted using this new format style, you would get: “8:00″, or “08:00″, or “8:00 AM” when you hit Return.
        If you enter, “1134″, you would get, “11:34″ or “11:34 AM”
        If you enter, “1400″, you would get, “2:00 PM” (using military time conversion).
        If you enter, “1670″ (are you sitting down?), you would get: “5:10 PM” as it would take the time over 60 and add it to the current hour (16 = 4pm). Cool, huh?! Or, I suppose it could just default to “VALUE?” (but that’s too easy and boring!)

        A formula is one way of doing it, but it requires a separate entry and output address. I want to be able to enter a numeric value and have the cell format automatically convert the value to a time.

        • Skypenguin

          Here is the problem. Excel thinks of dates as a number from 1/1/1900. So when you convert your time 1115. It thinks you mean day 1115 past 1900. So time is just a fraction of the date.
          11:15 on 1/1/1900 would be 0.46875 (because you are almost half way through the day). 1/2/1900 would be 1.46875 etc..

          So Here is the issue with just changing the “format” excel thinks you mean something else when a time is 1115. For excel mind date 11:15 = general 0.46875

          general 1115 does not equal date 11:15

          You’re not actually just changing the number to a time you are recalculating the value to a time/date formula that excel understands. This is why you need a formula.

          Maybe you could do this with a visual basic script, but are you willing to take on that kind of project? I don’t think there are any options out of the box (in the GUI) that are going to reformat this without a formula or script. We had this same project at my office, but just decided it was easier to type in the colon than spend the programming time messing with it.

          I know this doesnt solve the problem, but I hope it helps explain the nature of why it’s not just a simple transformation without some programming.

  • Cindy

    I have a table of 10 digit numbers from a time clock app in my iphone. I took it to be repaired and forgot to export the file to excel. The tech exported it into some other type of file and the closest I can get is these 10 digit numbers. I was able to subtract one column from the other and divide by 60 twice and came up with what would be the time worked. Is there a way to convert that 10 digit number to a date and time? Here is an example that should probably be March 1st –
    1361297220

  • Isak Ajsuk Larsson

    in short, convert your cell to DAYS and then use the h:mm or hh:mm:ss format.