101 Excel 2013 Tips, Tricks and Timesavers (16 page)

Figure 36-2:
Avoid a two-column lookup by combing two columns into one.

After you create this new table, you can use a simpler formula to perform the lookup:

=VLOOKUP(Make&Model,H2:I12,2)


Tip 37: Calculating Holidays

Determining the date for a particular holiday can be tricky. Some holidays, such as New Year’s Day and Independence Day (U.S.), are no-brainers because they always occur on the same date. For these kinds of holidays, you can simply use the DATE function. For example, to calculate New Year’s Day (which always falls on January 1) for a specific year stored in cell A1, you can enter this function:

=DATE(A1,1,1)


Other holidays are defined in terms of a particular occurrence of a particular weekday in a particular month. For example, Labor Day in the U.S. falls on the first Monday in September.

The formulas that follow all assume that cell A1 contains a year value (for example, 2013). Notice that because New Year’s Day, Independence Day, Veterans Day, and Christmas Day all fall on the same days of the year, their dates can be calculated by using the simple DATE function.

Figure 37-1 shows a workbook that contains all of these formulas.

Figure 37-1:
Formulas calculate the dates of holidays for the year in cell A1.

New Year’s Day

This holiday always falls on January 1:

=DATE(A1,1,1)


Martin Luther King Jr. Day

This holiday occurs on the third Monday in January. The following formula calculates Martin Luther King Jr. Day for the year in cell A1:

=DATE(A1,1,1)+IF(2

+2,2-WEEKDAY(DATE(A1,1,1)))+((3-1)*7)


Presidents’ Day

Presidents’ Day occurs on the third Monday in February. This formula calculates Presidents’ Day for the year in cell A1:

=DATE(A1,2,1)+IF(2

+2,2-WEEKDAY(DATE(A1,2,1)))+((3-1)*7)


Easter

Calculating the date for Easter is difficult because of the complicated manner in which Easter is determined. Easter Day is the first Sunday after the next full moon occurs after the vernal equinox. I found these formulas to calculate Easter on the web. I have no idea how they work. They don’t work if your workbook uses the 1904 date system.

=DOLLAR((“4/”&A1)/7+MOD(19*MOD(A1,19)-7,30)*14%,)*7-6


This one is slightly shorter, but equally obtuse:

=FLOOR(“5/”&DAY(MINUTE(A1/38)/2+56)&”/”&A1,7)-34


Memorial Day

The last Monday in May is Memorial Day. This formula calculates Memorial Day for the year in cell A1:

=DATE(A1,6,1)+IF(2

+2,2-WEEKDAY(DATE(A1,6,1)))+((1-1)*7)-7


Notice that this formula calculates the first Monday in June and then subtracts 7 from the result, to return the last Monday in May.

Independence Day

The Independence Day holiday always falls on July 4:

=DATE(A1,7,4)


Labor Day

Labor Day occurs on the first Monday in September. This formula calculates Labor Day for the year in cell A1:

=DATE(A1,9,1)+IF(2

+2,2-WEEKDAY(DATE(A1,9,1)))+((1-1)*7)


Columbus Day

The Columbus Day holiday occurs on the second Monday in October. The following formula calculates Columbus Day for the year in cell A1:

=DATE(A1,10,1)+IF(2

+2,2-WEEKDAY(DATE(A1,10,1)))+((2-1)*7)


Veterans Day

The Veterans Day holiday always falls on November 11:

=DATE(A1,11,11)


Thanksgiving Day

Thanksgiving Day is celebrated on the fourth Thursday in November. This formula calculates Thanksgiving Day for the year in cell A1:

=DATE(A1,11,1)+IF(5

+5,5-WEEKDAY(DATE(A1,11,1)))+((4-1)*7)


Christmas Day

Christmas Day always falls on December 25:

=DATE(A1,12,25)


Tip 38: Calculating a Person’s Age

Calculating a person’s age is a bit tricky because the calculation depends on not only the current year but also the current day. And then you have to consider the complications resulting from leap years.

In this tip, I present three methods to calculate a person’s age. These formulas assume that cell B1 contains the date of birth (for example, 2/16/1952) and that cell B2 contains the current date (calculated with the TODAY function).

Method 1

The following formula subtracts the date of birth from the current date and divides by 365.25. The INT function then eliminates the decimal part of the result:

=INT((B2-B1)/365.25)


This formula isn’t 100 percent accurate because it divides by the average number of days in a year. For example, consider a child who is exactly one year old. This formula returns 0, not 1.

Method 2

A more accurate way to calculate age uses the YEARFRAC function:

=INT(YEARFRAC(B2, B1))


The YEARFRAC function is normally used in financial calculations, but it works just fine for calculating ages. This function calculates the fraction of the year represented by the number of whole days between two dates. Using the INT function eliminates the fraction and returns an integer that represents full years.

Method 3

The third method for calculating age uses the DATEDIF function. This undocumented function isn’t described in the Excel Help system:

=DATEDIF(B1,B2,”Y”)


If you’re a stickler for accuracy, here’s another version:

=DATEDIF(B1,B2,”y”) & “ years, “&DATEDIF(B1,B2,”ym”) &


“ months, “&DATEDIF(B1,B2,”md”) & “ days”


This function returns a text string, like this:

33 years, 8 months, 17 days


Tip 39: Working with Pre-1900 Dates

According to Excel, the world began on January 1, 1900. If you work with historical information or do genealogy research, you may have noticed that Excel doesn’t recognize pre-1900 dates. For example, if you enter July 4, 1776, into a cell, Excel interprets it as text, not a date.

Unfortunately, the only way to work with pre-1900 dates is to enter the date into a cell as text. The problem, however, is that you can’t perform any manipulation on dates recognized as text. For example, you can’t change its numeric formatting, you can’t determine which day of the week this date occurred on, and you can’t calculate the date that occurs seven days later.

Use three columns

To be able to sort by dates that precede 1900, enter the year, month, and day into separate cells. Figure 39-1 shows a simple example.

Figure 39-1:
To allow sorting by pre-1900 dates, enter the year, month, and day into separate cells.

To sort the presidents by birthday, first do an ascending sort on column D (day), an ascending sort on column C (month), and finally, an ascending sort on column B (year). The result is shown in Figure 39-2.

Unfortunately, you can’t perform other date-related operations for these pre-1900 dates. For example, you cannot perform subtraction to determine ages.

Figure 39-2:
The presidents sorted by birthday, after performing three sorts.

Use custom functions

I created an Excel add-in, XDATE, which contains a number of functions written in VBA. These functions enable you to work with dates in the years 0100 through 9999. You can download a free copy from my website:
http://spreadsheetpage.com
.

The extended date functions are

→
XDATE(y,m,d,fmt):
Returns a date for a given year, month, and day. As an option, you can provide a date formatting string.

→
XDATEADD(xdate1,days,fmt):
Adds a specified number of days to a date. As an option, you can provide a date formatting string.

→
XDATEDIF(xdate1,xdate2):
Returns the number of days between two dates.

→
XDATEYEARDIF(xdate1,xdate2):
Returns the number of full years between two dates (useful for calculating ages).

→
XDATEYEAR(xdate1):
Returns the year of a date.

→
XDATEMONTH(xdate1):
Returns the month of a date.

→
XDATEDAY(xdate1):
Returns the day of a date.

→
XDATEDOW(xdate1):
Returns the day of the week of a date (as an integer between 1 and 7).

Figure 39-3 shows a worksheet that uses some of these custom functions.