The capability to pick out a specific aspect of a date is often very helpful. For example, you may need to filter all records that have order dates within a certain month, or all employees that have time allocated to Saturdays. In such situations, you would need to pull out the month and workday number from the formatted dates.
Excel provides a simple set of functions to parse dates out into their component parts. These functions are:
YEAR: Extracts the year from a given date
MONTH: Extracts the month from a given date
DAY: Extracts the month day number from a given date
WEEKDAY: Returns the weekday number for a given date
WEEKNUM: Returns the week number for a given date
The figure demonstrates the use of these functions to parse the date in cell C3 into its component parts.
These functions are fairly straightforward.
The YEAR function returns a four-digit number that corresponds to the year of a specified date. This formula returns 2015:
=YEAR("5/16/2015")
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. The following 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 specify which day of the week defines the start of the week. By default, the WEEKDAY function defines the start of the week as Sunday. As you enter the WEEKDAY function, Excel activates a tooltip through which you can select a different return_type code.
You can adjust the formula so that the return values 1 through 7 represent Monday through Sunday. In the following case, the formula using a 1 as the optional argument tells Excel that the week starts on Monday and ends on Sunday. Because May 16, 2015, falls on a Saturday, the formula returns the number 6.
=WEEKDAY("5/16/2015", 1)
The WEEKNUM function returns the week number within which the specified date falls. This formula returns 20 because 5/16/2015 falls within week number 20 in 2015.
=WEEKNUM("5/16/2015")