How to Extract Parts of a Date in Excel

Dates are one of the most common types of data that we encounter in Excel. They can be used for various purposes, such as tracking deadlines, scheduling events, calculating durations, and analyzing trends. However, sometimes we may need to extract specific parts of a date, such as the year, month, day, weekday, or quarter. For example, we may want to group sales data by month, or compare the performance of different quarters.

Fortunately, Excel provides several built-in functions that can help us extract parts of a date easily and accurately. In this article, we will learn how to use these functions and apply them to a real-world scenario.

The Basic

Before we dive into the functions, we need to understand how Excel stores and displays dates. Excel stores dates as serial numbers, which represent the number of days that have passed since January 1, 1900. For example, the date January 1, 2021 is stored as 44197, because it is 44,197 days after January 1, 1900. The date February 2, 2024 is stored as 46038, because it is 46,038 days after January 1, 1900.

Excel displays dates in various formats, depending on the regional settings and the number format applied to the cells. For example, the date January 1, 2021 can be displayed as 1/1/2021, 01/01/2021, 1-Jan-2021, or January 1, 2021. However, these are just different ways of showing the same underlying serial number.

To extract parts of a date, we need to use functions that can recognize the serial number and return the desired component. Excel has several functions that can do this, such as YEAR, MONTH, DAY, WEEKDAY, and QUARTER. These functions take a date (or a cell reference that contains a date) as an argument and return a number that corresponds to the part of the date. For example, the function YEAR(1/1/2021) returns 2021, the function MONTH(1/1/2021) returns 1, and the function DAY(1/1/2021) returns 1.

Procedures

To use the functions to extract parts of a date, we need to follow these steps:

1. Enter the date (or a cell reference that contains a date) as the argument of the function. For example, to extract the year from the date in cell A2, we can enter =YEAR(A2) in another cell.
2. Press Enter to get the result. For example, if cell A2 contains the date 1/1/2021, the formula =YEAR(A2) will return 2021.
3. Repeat the same process for other parts of the date, using the appropriate function. For example, to extract the month from the date in cell A2, we can enter =MONTH(A2) in another cell, and it will return 1.

Explanation

To illustrate how to extract parts of a date in Excel, let us consider a scenario where we have a table of sales data for a company. The table contains the date, product, quantity, and price of each sale. Here is a sample of the table:

We want to extract the year, month, day, weekday, and quarter from the date column, and add them as new columns to the table. This will allow us to analyze the sales data by different time periods and dimensions. Here is how we can do this:

1. In cell E2, enter the formula =YEAR(A2) to extract the year from the date in cell A2. Press Enter to get the result, which is 2021.
2. In cell F2, enter the formula =MONTH(A2) to extract the month from the date in cell A2. Press Enter to get the result, which is 1.
3. In cell G2, enter the formula =DAY(A2) to extract the day from the date in cell A2. Press Enter to get the result, which is 1.
4. In cell H2, enter the formula =WEEKDAY(A2) to extract the weekday from the date in cell A2. Press Enter to get the result, which is 6. This means that the date in cell A2 is a Friday, because Excel assigns numbers from 1 to 7 to the weekdays, starting from Sunday. If we want to get the name of the weekday instead of the number, we can use the TEXT function, such as =TEXT(A2,“dddd”), which will return Friday.
5. In cell I2, enter the formula =QUARTER(A2) to extract the quarter from the date in cell A2. Press Enter to get the result, which is 1. This means that the date in cell A2 is in the first quarter of the year. The QUARTER function is not a built-in function in Excel, but we can create it using the CEILING function, such as =CEILING(MONTH(A2)/3,1), which will round up the month number to the nearest multiple of 3 and divide it by 3.

The table now looks like this:

We can then copy and paste the formulas in cells E2 to I2 to the rest of the rows, using the fill handle or the shortcut Ctrl+D. This will apply the same functions to the other dates in column A, and extract the corresponding parts of the date.