Convert times

Microsoft Office Excel 2003

previous page next page

Use the CONVERT function to do this task.

If this function is not available, install and load the Analysis ToolPak add-in.

Show How?

  1. On the Tools menu, click Add-Ins.
  2. In the Add-Ins available list, select the Analysis ToolPak box, and then click OK.
  3. If necessary, follow the instructions in the setup program.

Worksheet example

The example may be easier to understand if you copy it to a blank worksheet.

ShowHow?

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic. Do not select the row or column headers.

    Selecting an example from Help

    Selecting an example from Help

  3. Press CTRL+C.
  4. In the worksheet, select cell A1, and press CTRL+V.
  5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
 
1
2
A
Data
6
Formula Description (Result)
=CONVERT(A2,"day","hr") Convert 6 days to hours (144)
=CONVERT(A2,"hr","mn") Convert 6 hours to minutes (360)
=CONVERT(A2,"yr", "day") Convert 6 years to days (2191.5)

Note  For conversions using a year, a year is treated as 365.25 days.

Function details

CONVERT

ShowConvert hours from standard time format (hours : minutes : seconds) to a decimal number

Use INT function to do this task.

Worksheet example

The example may be easier to understand if you copy it to a blank worksheet.

ShowHow?

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic. Do not select the row or column headers.

    Selecting an example from Help

    Selecting an example from Help

  3. Press CTRL+C.
  4. In the worksheet, select cell A1, and press CTRL+V.
  5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
 
1
2
3
A
Time
10:35 AM
12:15 PM
Formula Description (Result)
=(A2-INT(A2))*24 Number of hours since 12:00 AM (10.583333)
=(A3-INT(A3))*24 Number of hours since 12:00 AM (12.25)

Function details

INT

ShowConvert hours from decimal number to the standard time format (hours : minutes : seconds)

Use the divisor operator (/) and the TEXT function to do this task.

Worksheet example

The example may be easier to understand if you copy it to a blank worksheet.

Show How?

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic. Do not select the row or column headers.

    Selecting an example from Help

    Selecting an example from Help

  3. Press CTRL+C.
  4. In the worksheet, select cell A1, and press CTRL+V.
  5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
 
1
2
3
A
Hours
10.5833
12.25
Formula Description (Result)
=TEXT(A2/24, "h:mm") Hours since 12:00 AM (10:35)
=TEXT(A3/24, "h:mm") Hours since 12:00 AM (12:15)

Note  You can also apply the time number format without using the TEXT function to specify the format. To view the number as a time, select the cell and click Cells on the Format menu. Click the Number tab, and then click Time in the Category box. However, if you use both a format applied with the TEXT function and number format, the TEXT function takes precedence.

Function details

TEXT

previous page start next page