Microsoft Office Tutorials and References

In Depth Information

**Mastering Excel’s Built-in Functions**

You can perform date arithmetic by nesting these functions in formulas containing

references to other cells containing dates. If cell E17 contains the date when you prepared and

sent an invoice that has not yet been paid, you can calculate the number of days since that

invoice was sent by using this formula:

=TODAY()–E17

TROUBLESHOOTING

Performing date arithmetic returns a date instead of a number

One quirk of date arithmetic in worksheets is that Excel tries, helpfully, to apply date

formats when you use a date function in a formula. If you enter the formula =TODAY()–

E17, where E17 is a date 76 days earlier, the result is displayed as April 10, 1900. That’s

the equivalent date for the serial number 76. The ix? Press Ctrl+Shift+~ (tilde) to apply

the General format to the cell.

What if you’re using a worksheet that has one or more columns of data containing dates

or times formatted as text? If you’re unwilling or unable to reformat those cells, use the

DATE VALUE or TIMEVALUE functions to do the conversion. If the value is recognizable as a

date or time, the respective functions should work properly.

The list of functions under the Date & Time category heading includes a slew of esoteric

entries. For example, the DAYS360 function (with European and U.S. options) is used for

accounting systems that use a year made up of 12 30-day months. The EOMONTH function

helps you calculate the last day of a month that is a specified number of months before or

after a target date—a handy trick because Excel can’t rely on “30 days has September, April,

June, and November” like humans can. If you’re doing calculations where the result must

be a working day and not a weekend, use the WORKDAY (or WORKDAY.INTL) function.

Text Functions

We confess to having a soft spot for the functions in this category. After all, we’re writers

and information managers, not accountants, so any tricks that make it easier to manipulate

lists and blocks of text are always handy. In fact, we regularly use the functions we discuss in

this section, singly or in combination, to split the output from database programs into

different fields for use in Excel or Word tables.

The most common way to use the functions in this group is to paste a list from another

source into one or more columns in Excel, and then use formulas in adjacent columns to

manipulate that text. In this section, we focus strictly on the functions that are most useful

for cleaning up and converting text.