Parsing dates using Excel's DAY, MONTH, YEAR, and WEEKDAY functions

Now that you've got a handle on date storage in Excel with the DATE function, it's time to learn about four more critical time manipulation tools: the DAY, WEEKDAY, MONTH, and YEAR functions. These functions are used to take a date (inputted as a date-formatted or date-serialized number) and find the day, weekday (e.g., Monday; Tuesday; etc.), month, or year of the date specified.

The DAY function

The DAY function takes a date-formatted or serialized input and outputs the day number of the specified date. Its syntax is as follows:

=DAY(serial_number)

DAY can be used on a serial number, or on a string formatted as a date, like so (note that the serialized date number for 5/6/2019 is 43591):

=DAY("5/6/2019")
Output: 6

=DAY(43591)
Output: 6

Let's take a look at the DAY function used in action on a variety of date formats:

=DAY(B3)
Output: 6

As you can see, DAY works on all of the above, whether the date is inputted as 5/6/2019 or 6-May-19 or 43591.

The WEEKDAY function

Like DAY, WEEKDAY takes a date-formatted or serialized input; however, rather than outputting a day number, it outputs the day of the week (e.g., Monday, Tuesday, etc.). Its syntax is as follows:

=WEEKDAY(serial_number, return_type (optional))

By default, WEEKDAY will output the weekday as an integer from 1 to 7, with 1 representing Sunday; 2 representing Monday; 3 representing Tuesday; etc. Take a look at the following example, in which we use WEEKDAY to find the weekday of May 6, 2019 (a Monday):

=WEEKDAY("5/6/2019")
Output: 2

The above formula outputs 2, because by default, WEEKDAY represents Monday as the integer 2.

The optional return_type parameter lets Excel know where in the week to start when using integers to represent weekdays. Per the above, if left out, Excel will represent Sunday as a 1 and Saturday as 7. However, there are several other options:

Here's WEEKDAY used on a variety of date formats:

=WEEKDAY(B3)
Output: 6

Per the above, we know how to use WEEKDAY to extract a weekday value equivalent. But what if we want the names of weekdays to appear in plain English? To do this, use the TEXT function with a format_text argument of either "ddd" (to represent short day names, like "Mon" or "Tue") or "dddd" (to represent long day names, like "Monday" or "Tuesday"). Check out the examples below:

=TEXT(WEEKDAY("5/6/2019"), "ddd")
Output: "Mon"

=TEXT(WEEKDAY("5/6/2019"), "dddd")
Output: "Monday"

=TEXT(WEEKDAY(B3),"dddd")
Output: "Monday"

The MONTH and YEAR functions

By now, you've probably guessed how the MONTH and YEAR functions work: by taking a date-formatted number and outputting its month. Here's are their syntax:

=MONTH(date)

=YEAR(date)

Let's take a look at a couple examples of MONTH and YEAR in action:

=MONTH("5/6/2019")
Output: 5

=MONTH(43591)
Output: 5

=YEAR("5/6/2019")
Output: 2019

=YEAR(43591)
Output: 2019

And here are MONTH and YEAR used on our list of dates from above:

=MONTH(B3)
Output: 5

=YEAR(B3)
Output: 2019

Those are the basics of Excel's DAY, WEEKDAY, MONTH, and YEAR functions! If you have questions or comments on the above, be sure to let us know in the comments section below.

Comments