Microsoft Office Tutorials and References

In Depth Information

**11. Using Everyday Functions: Math, Date and Time, and Text Functions**

11.

11. Using

Using Everyday

Everyday Functions:

Functions: Math,

Math, Date

Date and

and Time,

Time, and

and

Text Functions

Excel offers many functions for dealing with basic math, dates and times, and

text. This chapter describes the functions found under the Text icon, the Date

& Time icon, and the Math portion of the Math & Trig icon on the Formulas tab.

Functions
.
”

A few of the new functions in Excel 2013 fall into this chapter:

•
NUMBERVALUE

NUMBERVALUE
—
Converts text numbers to their number value, even

when the text contains foreign punctuation. For example, your country

might show numbers as $1,234.56. A text file from a foreign subsidiary

might include that same number with punctuation of $1.234,56. The new

NUMBERVALUE provides a formulaic way to convert the text to num-

bers. This might be useful if there was some reason you could not use

Text to Columns.

•
ARABIC

ARABIC
—
Converts Roman numerals stored as text back to numbers.

This might be useful if you cannot figure out that Super Bowl XLIX is

the 49th time the game has been played. Actually,

=ROMAN(ARABIC(A1)+1) is a nice way to increment Roman numerals.

•
COMBIN

COMBINA
—
These functions provide a way to calculate

combinations and to calculate combinations where repeats are al-

lowed, respectively. They join PERMUT and PERMUATIONA.

•
UNICHAR

COMBIN and COMBINA

UNICODE
—
These functions extend the functionality of

CHAR and CODE, respectively, beyond the 255 ASCII character set.

•
DAYS

UNICHAR and UNICODE

DAYS
—
Calculates the number of days between two dates. This func-

tion works with dates stored as text in addition to regular dates, and it

prevents you from having to use

=IF(ISNUMBER(A2),A2,DATEVALUE(A2)) to convert a text date to a date.

•
ISOWEEKNUM

ISOWEEKNUM
—
Calculates the ISO week number. In the ISO system, a

year is made up of either 52 or 53 full weeks (364 or 371 days). Weeks

start on Monday. The week that contains the first Thursday of the

year is numbered Week 1.