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.

Before reading through this tutorial, it's critical that you familizarize yourself with Excel's date formatting and serialization techniques. If you need to do so, head on over to our DATE function overview first.

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:

The DAY function used on a serialized date
=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:

Return_type argument Meaning
1 (or no return_type specified) Sunday (1) through Saturday (7)
2 Monday (1) through Sunday (7)
3 Monday (0) through Sunday (6)
12 Tuesday (1) through Monday (7)
13 Wednesday (1) through Tuesday (7)
14 Thursday (1) through Wednesday (7)
15 Friday (1) through Thursday (7)
16 Saturday (1) through Friday (7)

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

The WEEKDAY function used on a serialized date
=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"
WEEKDAY combined with TEXT
=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 used on a range of dates
=MONTH(B3)
Output: 5
YEAR used on a range of dates
=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.

Save three hours of work a day with these 5 simple Excel hacks

Work smarter, not harder. Sign up for our 5-day mini-course to receive must-learn lessons on how to automate your work using Excel.

  • Why the TRUE and FALSE functions are so important...
  • Why to rethink the way you do VLOOKUPs...
  • Plus, we'll reveal why you shouldn't use PivotTables and what to use instead...

By submitting this information, you agree to Deskbright's privacy policy.

Comments