|
|
|
This page presents how to work with Microsoft Excel 2007 features and its redesigned interface. You may also visit our Excel Question page.
|
Convert times
Let's say you want to find out how many hours there are in five and a half days, or how many days there are in 3.45 years, or how many months have passed between a beginning date and an ending date. There are several ways to convert times.
What do you want to do?
Convert between time units
Convert hours from standard time format (hours : minutes : seconds) to a decimal number
Convert hours from decimal number to the standard time format (hours : minutes : seconds)
Convert between time units
To do this task, use the CONVERT function.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
- Create a blank workbook or worksheet.
- Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
- Press CTRL+C.
- In the worksheet, select cell A1, and press CTRL+V.
- To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.
|
|
|
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
Top of Page
Convert hours from standard time format (hours : minutes : seconds) to a decimal number
To do this task, use the INT function.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
- Create a blank workbook or worksheet.
- Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
- Press CTRL+C.
- In the worksheet, select cell A1, and press CTRL+V.
- To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.
|
|
|
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
Top of Page
Convert hours from decimal number to the standard time format (hours : minutes : seconds)
To do this task, use the divisor operator (/) and the TEXT function.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
- Create a blank workbook or worksheet.
- Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
- Press CTRL+C.
- In the worksheet, select cell A1, and press CTRL+V.
- To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.
|
|
|
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 format without using the TEXT function to specify the format. To view the number as a time, select the cell, and on the Home tab, in the Number group, click the arrow next to the Number Format box, click More Number Formats, click Custom in the Category list, and then click a custom format in the Type box. However, if you use both a format applied with the TEXT function and number format, the TEXT function takes precedence.
Function details
TEXT
Top of Page
Excel > Formula and name basics > Examples of formulas > Conversion
Convert measurements
Let's say you want to know how many kilometers that you ran on a quarter-mile track or a tolerance level using metric values. To convert measurements, use the CONVERT function. The CONVERT function converts a wide range of measurements, including measures of weight, distance, time, pressure, force, energy, power, magnetism, temperature, and liquid measure.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
- Create a blank workbook or worksheet.
- Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
- Press CTRL+C.
- In the worksheet, select cell A1, and press CTRL+V.
- To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.
|
|
|
A
|
|
|
Data
|
|
6
|
|
Formula
|
Description (Result)
|
|
=CONVERT(A2,"C","F")
|
Convert 6 degrees Celsius to Fahrenheit (42.8)
|
|
=CONVERT(A2,"tsp","tbs")
|
Convert 6 teaspoons to tablespoons (2)
|
|
=CONVERT(A2,"gal","l")
|
Convert 6 gallons to liters (22.71741274)
|
|
=CONVERT(A2,"mi","km")
|
Convert 6 miles to kilometers (9.656064)
|
|
=CONVERT(A2,"km","mi")
|
Convert 6 kilometers to miles (3.728227153)
|
|
=CONVERT(A2,"in","ft")
|
Convert 6 inches to feet (0.5)
|
|
=CONVERT(A2,"cm","in")
|
Convert 6 centimeters to inches (2.362204724)
|
|
Note To see a full list of all the measures that can be converted, see the CONVERT function help topic.
Function details
CONVERT
Excel > Formula and name basics > Examples of formulas > Conversion
Convert numbers to different number systems
Excel 2007
A number system is a systematic way to represent numbers with symbolic characters and uses a base value to conveniently group numbers in compact form. The most common number system is decimal, which has a base value of 10, and a symbolic character set of 0, 1, 2, 3, 4, 5, 6, 7, 8, and 9. However, there are other number systems, and they can be more efficient to use for a specific purpose. For example, because computers use Boolean logic to perform calculations and operations, they use the binary number system, which has a base value of 2.
Microsoft Office Excel has several functions that you can use to convert numbers to and from the following number systems:
|
Number system
|
Base value
|
Symbolic character set
|
|
Binary
|
2
|
0,1
|
|
Octal
|
8
|
0, 1, 2, 3, 4, 5, 6, 7
|
|
Decimal
|
10
|
0, 1, 2, 3, 4, 5, 6, 7, 8, and 9
|
|
Hexadecimal
|
16
|
0, 1, 2, 3, 4, 5, 6, 7, 8, 9, A, B, C, D, E, F
|
What do you want to do?
Convert a binary number to decimal
Convert a binary number to hexadecimal
Convert a binary number to octal
Converts a decimal number to binary
Convert a decimal number to hexadecimal
Convert a decimal number to octal
Convert a hexadecimal number to binary
Convert a hexadecimal number to decimal
Convert a hexadecimal number to octal
Convert an octal number to binary
Convert an octal number to decimal
Convert an octal number to hexadecimal
Convert a binary number to decimal
To do this task, use the BIN2DEC function.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
- Create a blank workbook or worksheet.
- Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
- Press CTRL+C.
- In the worksheet, select cell A1, and press CTRL+V.
- To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.
|
|
|
A
|
B
|
|
Formula
|
Description (Result)
|
|
=BIN2DEC(1100100)
|
Converts binary 1100100 to decimal (100)
|
|
=BIN2DEC(1111111111)
|
Converts binary 1111111111 to decimal (-1)
|
|
Function details
BIN2DEC
Top of Page
Convert a binary number to hexadecimal
To do this task, use the BIN2HEX function.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
- Create a blank workbook or worksheet.
- Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
- Press CTRL+C.
- In the worksheet, select cell A1, and press CTRL+V.
- To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.
|
|
|
A
|
B
|
|
Formula
|
Description (Result)
|
|
=BIN2HEX(11111011, 4)
|
Converts binary 11111011 to hexadecimal with 4 characters (00FB)
|
|
=BIN2HEX(1110)
|
Converts binary 1110 to hexadecimal (E)
|
|
=BIN2HEX(1111111111)
|
Converts binary 1111111111 to hexadecimal (FFFFFFFFFF)
|
|
Function details
BIN2HEX
Top of Page
Convert a binary number to octal
To do this task, use the BIN2OCT function.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
- Create a blank workbook or worksheet.
- Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
- Press CTRL+C.
- In the worksheet, select cell A1, and press CTRL+V.
- To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.
|
|
|
A
|
B
|
|
Formula
|
Description (Result)
|
|
=BIN2OCT(1001, 3)
|
Converts binary 1001 to octal with 3 characters (011)
|
|
=BIN2OCT(1100100)
|
Converts binary 1100100 to octal (144)
|
|
=BIN2OCT(1111111111)
|
Converts binary 1111111111 to octal (7777777777)
|
|
Function details
BIN2OCT
Top of Page
Converts a decimal number to binary
To do this task, use the DEC2BIN function.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
- Create a blank workbook or worksheet.
- Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
- Press CTRL+C.
- In the worksheet, select cell A1, and press CTRL+V.
- To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.
|
|
|
A
|
B
|
|
Formula
|
Description (Result)
|
|
=DEC2BIN(9, 4)
|
Converts decimal 9 to binary with 4 characters (1001)
|
|
=DEC2BIN(-100)
|
Converts decimal -100 to binary (1110011100)
|
|
Function details
DEC2BIN
Top of Page
Convert a decimal number to hexadecimal
To do this task, use the DEC2HEX function.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
- Create a blank workbook or worksheet.
- Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
- Press CTRL+C.
- In the worksheet, select cell A1, and press CTRL+V.
- To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.
|
|
|
A
|
B
|
|
Formula
|
Description (Result)
|
|
=DEC2HEX(100, 4)
|
Converts decimal 100 to hexadecimal with 4 characters (0064)
|
|
=DEC2HEX(-54)
|
Converts decimal -54 to hexadecimal (FFFFFFFFCA)
|
|
Function details
DEC2HEX
Top of Page
Convert a decimal number to octal
To do this task, use the DEC2OCT function.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
- Create a blank workbook or worksheet.
- Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
- Press CTRL+C.
- In the worksheet, select cell A1, and press CTRL+V.
- To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.
|
|
|
A
|
B
|
|
Formula
|
Description (Result)
|
|
=DEC2OCT(58, 3)
|
Converts decimal 58 to octal (072)
|
|
=DEC2OCT(-100)
|
Converts decimal to octal (7777777634)
|
|
Function details
DEC2OCT
Top of Page
Convert a hexadecimal number to binary
To do this task, use the HEX2BIN function.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
- Create a blank workbook or worksheet.
- Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
- Press CTRL+C.
- In the worksheet, select cell A1, and press CTRL+V.
- To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.
|
|
|
A
|
B
|
|
Formula
|
Description (Result)
|
|
=HEX2BIN("F", 8)
|
Converts hexadecimal F to binary, with 8 characters (00001111)
|
|
=HEX2BIN("B7")
|
Converts hexadecimal B7 to binary (10110111)
|
|
=HEX2BIN("FFFFFFFFFF")
|
Converts hexadecimal FFFFFFFFFF to binary (1111111111)
|
|
Function details
HEX2BIN
Top of Page
Convert a hexadecimal number to decimal
To do this task, use the HEX2DEC function.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
- Create a blank workbook or worksheet.
- Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
- Press CTRL+C.
- In the worksheet, select cell A1, and press CTRL+V.
- To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.
|
|
|
A
|
B
|
|
Formula
|
Description (Result)
|
|
=HEX2DEC("A5")
|
Converts hexadecimal A5 to decimal (165)
|
|
=HEX2DEC("FFFFFFFF5B")
|
Converts hexadecimal FFFFFFFF5B to decimal (-165)
|
|
=HEX2DEC("3DA408B9")
|
Converts hexadecimal 3DA408B9 to decimal (1034160313)
|
|
Function details
HEX2DEC
Top of Page
Convert a hexadecimal number to octal
To do this task, use the HEX2OCT function.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
- Create a blank workbook or worksheet.
- Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
- Press CTRL+C.
- In the worksheet, select cell A1, and press CTRL+V.
- To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.
|
|
|
A
|
B
|
|
Formula
|
Description (Result)
|
|
=HEX2OCT("F", 3)
|
Converts hexadecimal F to octal with 3 characters (017)
|
|
=HEX2OCT("3B4E")
|
Converts hexadecimal 3B4E to octal (35516)
|
|
=HEX2OCT("FFFFFFFF00")
|
Converts hexadecimal FFFFFFFF00 to octal (7777777400)
|
|
Function details
HEX2OCT
Top of Page
Convert an octal number to binary
To do this task, use the OCT2BIN function.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
- Create a blank workbook or worksheet.
- Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
- Press CTRL+C.
- In the worksheet, select cell A1, and press CTRL+V.
- To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.
|
|
|
A
|
B
|
|
Formula
|
Description (Result)
|
|
=OCT2BIN(3, 3)
|
Converts octal 3 to binary with 3 characters (011)
|
|
=OCT2BIN(7777777000)
|
Converts octal 7777777000 to binary (1000000000)
|
|
Function details
OCT2BIN
Top of Page
Convert an octal number to decimal
To do this task, use the OCT2DEC function.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
- Create a blank workbook or worksheet.
- Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
- Press CTRL+C.
- In the worksheet, select cell A1, and press CTRL+V.
- To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.
|
|
|
A
|
B
|
|
Formula
|
Description (Result)
|
|
=OCT2DEC(54)
|
Converts octal 54 to decimal (44)
|
|
=OCT2DEC(7777777533)
|
Converts octal 7777777533 to decimal (-165)
|
|
Function details
OCT2DEC
Top of Page
Convert an octal number to hexadecimal
To do this task, use the OCT2HEX function.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
- Create a blank workbook or worksheet.
- Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
- Press CTRL+C.
- In the worksheet, select cell A1, and press CTRL+V.
- To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.
|
|
|
A
|
B
|
|
Formula
|
Description (Result)
|
|
=OCT2HEX(100, 4)
|
Converts octal number 100 to hexadecimal with 4 characters (0040)
|
|
=OCT2HEX(7777777533)
|
Converts octal number 7777777533 to hexadecimal (FFFFFFFF5B)
|
|
Function details
OCT2HEX
Top of Page
Excel > Formula and name basics > Examples of formulas > Conversion
Convert Arabic to Roman numerals
Excel 2007
Let's say you want to change an Arabic numeral to a Roman numeral for special emphasis in a party invitation or promotional flyer. To convert Arabic to Roman numerals, use the ROMAN function.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
- Create a blank workbook or worksheet.
- Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
- Press CTRL+C.
- In the worksheet, select cell A1, and press CTRL+V.
- To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.
|
|
|
A
|
B
|
|
Formula
|
Description (Result)
|
|
=ROMAN(499,0)
|
Classic Roman numeral style for 499 (CDXCIX)
|
|
=ROMAN(499,1)
|
More concise version for 499 (LDVLIV)
|
|
=ROMAN(499,2)
|
More concise version for 499 (XDIX)
|
|
=ROMAN(499,3)
|
More concise version for 499 (VDIV)
|
|
=ROMAN(499,4)
|
More concise version for 499 (ID)
|
|
=ROMAN(2013,0)
|
Classic Roman numeral style for 2013 (MMXIII)
|
|
