How to get Month and Year or Days from Date in Excel

A very useful operation in Excel is cutting out a specific part of a date. Thanks to this, we can filter data lists for orders starting from a particular month or filter data about store employees who work on Sundays. For such tasks, you need to extract the month or the day of the week from the date.



How to Extract Specific Date Parts in Excel to Get YEAR, MONTH, and DAY?

To extract parts of a date in Excel, we have several simple yet valuable functions:

1. YEAR – returns the year from the specified date.
2. MONTH – selects the month from the original date.
3. DAY – allows you to obtain the day number within the month.
4. WEEKDAY – the function returns the day of the week number based on the date.
5. WEEKNUM – retrieves the week number in the year for the given date.

The image below clearly illustrates how each of these functions works, showing how the original date in cell A2 is divided into individual parts:

The functions described above are straightforward and easy to use.

The YEAR function returns a four-digit number representing the year.

MONTH returns a number from 1 to 12, which represents the month's position in the year.

DAY also returns a number, but this time between 1 and 31, indicating the day within the month.

WEEKDAY similarly returns a number indicating the day of the week from 1 (Sunday) to 7 (Saturday). By default, this function returns days starting from Sunday to Saturday. So, if it's 1, it's Sunday; 2 is Monday, and so on. This function also has an optional argument, "Return_Type". This argument helps specify which day should be considered the first day of the week. In the example, the number 2 was used as the second argument since, by default, the function starts counting from Sunday.

Note: Pay attention to the image and the value of 3 for the second argument. It's possible to set the weekdays from 0 (Monday) to 6 (Sunday). Such numbering is often used in programming, where numbering usually starts from zero.

This feature of the WEEKDAY function can be helpful for specific tasks in Excel.

Lastly, the WEEKNUM function returns the week number for the given date. For instance, both a leap year with 366 days (once every four years) and a regular year with 365 days have the same number of full weeks – 52.

Since the number also counts incomplete weeks, subtracting one might be necessary in the formula. The start of January is often the last week of the previous year.

In some leap years, the start of the year falls on a Saturday, and the end falls on a Sunday (once every 28 or 40 years), for instance, like in the year 2028.

Therefore, certain discrepancies might arise in consolidated reports. To address this, one can use the following formula:

=WEEKNUM(C3,IF(AND(MOD(YEAR(C3),4)=0,WEEKDAY(C3,2)=7),2,1))-1

WEEKNUM also has an optional second argument for the starting day of the week for the given date:

In the second optional argument, specify the day from which the week starts for the given year.

Interesting Fact: Every non-leap year starts and ends on the same day. For instance, 01.01.2018 and 31.12.2018 are Mondays. However, every leap year starts on one day and ends on the next. For example, 01.01.2020 is a Wednesday, while 31.12.2020 is a Thursday.

Practical Applications of Formulas for Date Separation

Date is a crucial indicator for data visualization. Therefore, it would be beneficial to make it manageable. For instance, on a dashboard, we may need to select specific days for analyzing accounting periods based on user-defined conditions. For example, the calendar functionality should account for the year, month, calendar days, and weeks. For convenience, an interactive calendar can be created to simultaneously manage the query condition and display results on dashboard charts.

To achieve this, we need to prepare a specific table of source data. Using data processing functions, we will separate dates into individual time measurement units: years, months, days:

This way, we will create flexibility in adjusting data query requests based on specific user conditions:

Download

Now, by using an interactive calendar, we control the source data on the chart by managing filters based on multiple conditions simultaneously. This widget is crafted in Excel using formulas and pivot table slices. No macros are involved! Such a useful calendar widget will always have its place on any dashboard for practical application. Since it's implemented without macros, it's effortless and convenient to integrate into report presentations.