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.

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.

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.

Now select the cell that contain result, and right-click to select 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.

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

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