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




Custom Search