<<

. 17
( 62 .)



>>

January 31 as being ˜˜February 0,™™ and February 28 as ˜˜March 0™™?
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




<<

. 17
( 62 .)



>>