Save an hour of work a day with these 5 advanced Excel tricks
Work smarter, not harder. Sign up for our 5-day mini-course to receive must-learn lessons on getting Excel to do your work for you.
How to create beautiful table formatting instantly...
Why to rethink the way you do VLOOKUPs...
Plus, we'll reveal why you shouldn't use PivotTables and what to use instead...
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 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:
1 (or no return_type specified)
Sunday (1) through Saturday (7)
Monday (1) through Sunday (7)
Monday (0) through Sunday (6)
Tuesday (1) through Monday (7)
Wednesday (1) through Tuesday (7)
Thursday (1) through Wednesday (7)
Friday (1) through Thursday (7)
Saturday (1) through Friday (7)
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: