Yes, in fact, it can. So now we have a solution for our dating

problem. We add an extra month to the month interval we want

to go up, but specify 0 for the DAY.

Let™s have 12/31/2003 in B1 again. In C1, to make the date

go up by one month, we write:

= DATE°YEAR°B1Þ,MONTH°B1Þ+2,0ÞÞ

to get ˜˜February 0, 2004,™™ which is 1/31/2004.

TLFeBOOK

Chapter 5

88

In D1, to continue to the next month, we write:

= DATE°YEAR°C1Þ,MONTH°C1Þ+2,0Þ

to get ˜˜March 0, 2004,™™ which is 2/29/2004.

Yearly Dating

Increasing dates by 1 year is fairly simple matter now. Just add 1

to the YEAR number. We will use the same table, with 12/31/03,

in cell B1. In cell C1, we would use the formula:

= DATE°YEAR°B1Þ+ 1,MONTH°B1Þ,DAY°B1ÞÞ

We will not have to worry about the DAY being off, since

we are dealing with the same month in the year, just a year apart.

However, if we are working with a February year-end, this

formula will not return the leap day of February 29 in the leap

years, since the DAY will always be based on the count of 28 for

2003 (or 29 if the starting year had been 2004). We could use the

approach of using the 0 day of the next month, however:

= DATE°YEAR°B1Þ+1,MONTH°B1Þ + 1,0Þ

This is the approach to use if you want the leap day to

appear on the leap years. Alternatively, instead of adding 1 to

the YEAR number, we can add 13 (12 þ 1) to the MONTH and

still use 0 for the DAY. That will bring us to exactly a year later.

Non-Annual Intervals

Using the MONTH part makes it easy to change the periodicity

of your model from annual to quarterlies, or to some other non-

annual interval. When you do this, it is a good idea always to use

the ˜˜plus 1™™ approach to the MONTH and use 0 for the DAY,

because the intervals you have can bring you to months with

dissimilar ending days.

TLFeBOOK

Your Model-Building Toolbox: Functions 89

Finding the Number of Days Between Dates

Because of the serial value system, it is easy to find the number

of days that have elapsed from one date to the next. For example,

to find the number of days between August 17, 1953 and

October 1, 2003, we do the following:

= DATE°2003,10,1Þ-DATE°1953,8,17Þ

which returns 18,307 days.

Alternatively, we can represent the actual date as a serial

value by using the DATEVALUE function, which converts the

text of the date to the serial value:

= DATEVALUE°“10=1=2003”Þ-DATEVALUE°“8=17=1953”Þ

which returns 18,307 days.

Finding the Number of Months Between Dates

Using DAYS360

Finding the number of months between two dates is a little

tricky, because of the different lengths of the months in the inter-

val. In the last example we could divide 18,307 by the average

number of days in a month (i.e., 365/12, or 30.42), but this is

inelegant. A better way is to use the DAYS360 function. With

DAYS360, Excel considers each year to be 360 days by assuming

that there are 12 months, each composed of 30 days. So Excel has

a way of considering the ending days of each month so that

everything falls into line properly. The syntax is:

= DAYS360°BeginningDate,EndingDateÞ

The beginning date can be one defined by the DATE func-

tion, but DAYS360 is also smart enough to take the text of the

dates:

= DAYS360°DATE°1953,8,17Þ,DATE°2003,10,1ÞÞ

TLFeBOOK

Chapter 5

90

which returns 18,044 days. Or

= DAYS360°“8=17=1953”,“10=1=2003”Þ

which returns 18,044 days.

Because each year is 5 days shorter than the actual year

(6 in a leap year), the number of interval days is fewer than

the previous calculation. This is not a problem if we are looking

to get a sense of the portion of the month or the year using this

method. To find the number of months, we simply take the inter-

val in days in a 360-day year and divide it by 30. Now, to get the

number of months or the number of years:

18,044=30= 601:47 months

18,044=360= 50:12 years

When to Use DAYS360 in a Model

DAYS360 is useful in calculating a portion of the year. Let™s say

that a transaction happens on June 14, and we just want to get a

value for the stub portion, or the portion of the year remaining

after the deal. Let™s assume a December 31 year-end:

= DAYS360°“6=14=2003”,“12=31=2003”Þ=360

which returns 197/360, or 0.55.

Solving a Problem with DAYS360

In some situations, DAYS360 does not give you a 30-day month.

Take the case where cell A1 has the date of 12/31/02 (the end of

December) and cell A2 has the date 2/28/03 (the end of February

in a non-leap year):

= DAYS360°A1,A2Þ

which returns 58 days.

TLFeBOOK

Your Model-Building Toolbox: Functions 91

Under the logic that each month is 30 days, the function

should return 60 days for the two full months™ interval. It does

not in this case because Excel looks at the end of the month and

tries to fit the 28-, 29-, 30-, 31-day endings into some order, and

somehow the 28-day ending is confusing it. We can help Excel

get unconfused by bringing the dates into the beginning of the

month, where the interval algorithm is more straightforward. We

do this by adding the number 1 to the cell references. In this way,

we also do not need to change the dates themselves:

DAYS360°A1+1,A2+1Þ= 60 days

Assuming that you are always using period-end dates,

adding 1 to the components of DAYS360 is a good way to

make sure that the function works properly.

FUNCTIONS FOR LOOKING UP DATA

Two of the functions for looking up data have been introduced as

variations of the IF functions: CHOOSE and OFFSET. Looking up

data is really pinpointing the location of the data point that you

want, whether it is from a collection of alternatives or from its

location as defined by rows and columns.

MATCH

Use MATCH if you are looking for the location of a specific

number or text in a range. Depending on how you write the

formula, this function will return either the row or column

number (but not both) within the range that you specified. The

syntax is:

= MATCH°LookUpValue,LookupArray,MatchTypeÞ

LookUpValue is the item that you want to look up. This can

be a number, a text, or a reference to another cell that holds the

LookUpValue.

LookUpArray is a contiguous range of cells, or a range name.

TLFeBOOK

Chapter 5

92

MatchType can be either 1, 0, or À1. If it is 1, the MATCH

will find the largest value that is less than or equal to the

LookUpValue. In this case, the items in the LookUpRange must be

arranged in ascending order.

If it is À1, it will find the smallest value that is greater than

or equal to the LookUpValue. The data must be in descending

order.

If it is 0, then it will find the exact match for LookUpValue.

The data can be in any order. MATCH is very useful in this

mode, and this is what we will illustrate below.

In this simple example, let™s find the location of the word

˜˜Bob™™ from the list of names:

A E G

D F

B C

1

2

Alice 2 =MATCH(“Bob”,B3:B6,0)

3

Bob

4

George

5

Bob

6

7

The argument ˜˜Bob™™ is written directly into the function

formula, but it could well have been a reference to another cell

that actually holds that word. The range in the middle specifies

the one-column block. The ˜˜0™™ at the end indicates we are look-

ing for the exact match for ˜˜Bob.™™

In this instance, the formula will return the value of 2,

meaning that it has found Bob in the second row of the target

range, which in this case is row 4 on the sheet. Note also that the

function disregards the second ˜˜Bob™™ altogether, because it has

already found the exact match in the first, and the function

essentially stops there.

If we made a slight change to the formula and made the

target range start at row 1: then the function will return 4, since

˜˜Bob™™ is now in the fourth row of this range. There is an inter-

esting point here. We have just identified the actual row number

in the sheet of where ˜˜Bob™™ is located (or at least the first

TLFeBOOK

Your Model-Building Toolbox: Functions 93