Chapter 12: Using Formulas with Dates and Times
12
FIGURE 12.6
Extract the parts of a date.
The MONTH function returns a number between 1 and 12 that corresponds to the month of a
specified date. This formula returns 5.
=MONTH("5/16/2015")The DAY function returns a number between 1 and 31 that corresponds to the day of the
month represented in a specified date. This formula returns 16.
=DAY("5/16/2015")The WEEKDAY function returns a number from 1 to 7 that corresponds to the day of the
week (Sunday through Saturday) on which the given date falls. If the date falls on a
Sunday, the number 1 is returned. If the date falls on a Monday, the number 2 is returned,
and so on. This formula returns 7 because 5/16/2015 falls on a Saturday.
=WEEKDAY("5/16/2015")This function actually has an optional return_type argument that lets you define which
day of the week holds the first position. As you enter the WEEKDAY function, Excel displays
a menu where you can select the appropriate return_type code.
You can adjust the formula so that the return values 1 through 7 represent Monday through
Sunday. In this case, the formula would return 6, so Saturdays are now tagged as the 6th
day of the week.
=WEEKDAY("5/16/2015",2)The WEEKNUM function returns the week number in the year for the week in which the
specified date occurs. This formula returns 20 because 5/16/2015 falls within week 20 in
2015.
=WEEKNUM("5/16/2015")This function actually has an optional return_type argument that lets you specify which
day of the week defines the start of the week. By default, the WEEKNUM function defines
the start of the week as Sunday. As you enter the WEEKNUM function, Excel displays a menu
where you can select a different return_type code.