In this post you will learn how to extract the year, month, day, weekday and week number from a date in Excel.
Extracting parts of a date –
Although it may seem trivial, it’s often helpful to pick out a specific part of a date. For
example, you may need to filter all records that have order dates within a certain month or
all employees who have time allocated to Saturdays. In these 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 as follows:
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.
Figure below 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. 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.