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:
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.
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.