Let’s say you work as a data analyst at a retail company and are tasked with analyzing annual sales data to observe trends over multiple years. Now, you need to create a new column that only contains the year of each transaction.
If you work with data, a lot you I am sure would know having a column with the year along with a date columns really helpful in data analysis. This will help summarize and compare annual sales performance to have a yearly sales analysis.
Well, there are multiple ways to write formulas for this. And, in this tutorial, we are going to learn all these formulas. So let’s get started.
Extract Year from a Date with YEAR Function
The YEAR function allows you to extract the year from a given date. It requires a valid date (refer from a cell or insert directly into the function) and returns a four-digit year.
To get the year from the date that you have in cell A1, use the following steps:
- First, edit cell B1, and enter the “=”.
- After that, enter the YEAR function and the starting parentheses.
- Now, refer to cell A1 where you have the date.
- In the end, enter closing parentheses and hit enter to get the result.
=YEAR(A1)Insert Date Directly into the Function
When you want to insert a date into the YEAR function directly, enclose it in double quotation marks (“4/15/2023”). For example, if you’re using this date in a formula, you can write:
=YEAR("4/15/2023")
=2023 But if you enter a date like below in the YEAR function, it won’t return a value year result.
=YEAR(4/15/2023) =1900
When you use dates in quotation marks, Excel treats them as text, so ensure your system’s date settings match the format you’re entering to avoid errors. The best way is to use “mm/dd/yyyy” format.
Use TEXT Function to Extract Year from Date
Apart from the function that we have discussed above, you can use the TEXT to get year and month from date. In the text function, you have two arguments to define, the first is the date, and the second is the format_text that you want to extract from the date.
In the following example, you have the text function with the format_text “YYYY” that returns the year in four digits.
If you want to get a year in two digits, you can use the following format_text.
In the end, you can use the value function to convert the result in an number instead of a text. So the formula, would be:
=VALUE(TEXT(A1,"YYYY")) =VALUE(TEXT(A1,"YY"))
Must Read – Separate Date and Time in Excel
Combine YEAR + DATE to Return Year from Date
The formula below is useful when the year, month, and day are three different cells. So when you use DATE, you can create a date using those values, and then the YEAR function gets the year from that date.
=YEAR(DATE(A3, A2, A1)) =YEAR(DATE(year, month, day))
This formula allows you to create dates dynamically based on other calculations and then get the year from it. For example, if the year, month, or day parts are results of other formulas or change based on conditions, this formula can create a date first and then an extract year.
Display a Date as a Year instead of Getting It
You can also use custom formatting to convert a date into a year or a month. This method shows only the year, or month for a date that you have in the cell.
- Select the cell or range of cells where you have the dates.
- Open the Format Options by using the Ctrl + 1.
- Click on the Custom option and then click on the type of input bar “YYYY”.
- And then click OK.
The moment you click OK, it shows you the year instead of the date. Even though the cell contains a complete date, it will only display the year.
Extract Month from a Date
If you have a date in cell A1 and you want to extract month it, use the following steps:
- First, in cell B1, enter “=”.
- After that enter the MONTH function and starting parentheses.
- Now, refer to cell A1 where you have the date.
- In the end, enter closing parentheses and hit enter to get the result.
=MONTH(A1)
Using TEXT Function
In the same way, you can use the format_text “MMM” to get the month value from the date.
To get a month in the full name.
And only get the number in the result.
Custom Formatting
You can enter the month format in the “Type” input bar to show the month instead of the date.
