Microsoft Office Tutorials and References

In Depth Information

**Breaking a Date with DAY, MONTH, and YEAR**

The day parts of the dates, in Column B, were key to these calculations:

✓
In cell E4 the calculation is =SUMIF(B2:B366,”<16”,C2:C366)/

COUNTIF(B2:B366”<16”).

✓
In cell E10 the calculation is =SUMIF(B2:B366,”>15”,C2:C366)/

COUNTIF(B2:B366,”>15”).

The SUMIF function is discussed in Chapter 8. The COUNTIF function is

discussed in Chapter 9.

The DAY function has been instrumental in showing that more customers visit

the fictitious store in the second half of the month. This type of information is

great for a store owner to plan staff assignments, sales specials, and so on.

Isolating the month

Isolating the month part of a date is useful in applications where just the

month, but not the day or year, is relevant. For example, you may have a list

of dates on which more than five of your employees call in sick and need to

determine if this is more common in certain months than others.

You could sort the dates and then count the number for each month. That

would be easy enough, but sorting may not be an option based on other

requirements. Besides, why manually count when you have, right in front of

you, one of the all-time greatest counting software programs ever made?

Figure 12-6 shows a worksheet in which the MONTH function has extracted

the numeric month value (1–12) into Column B from the dates in Column

A. Cell B2 contains the formula =MONTH(A2) and so on down the column.

Columns C and D contain a summary of dates per month. The formula used in

cell D3 is =COUNTIF($B$2:$B$260,1).

This counts the number of dates where the month value is 1 — in other

words, January. Cells D4 through D14 contain similar formulas for month

values 2 through 12. The figure’s data plot makes it clear that calling in sick

is more prevalent in December and January. See Chapter 9 for information on

the COUNTIF function.

Use the MONTH function this way:

1. Select the cell where you want the results displayed.

2. Enter
=MONTH(
to begin the function entry.