1. Home
  2. MS Office

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.

Leave a comment