Let’s say you manage a project and track its progress over various dates. You have a list of dates when key parts of the project were completed, but for your monthly report, you want to show your boss the month and year.
By converting the full dates into just the month and year format in Excel, you can easily organize your report, making it easy to see which tasks were completed in each specific month of the year.
In Excel, there are multiple functions that you can use to write formulas for this. And in this tutorial, we will learn each formula in detail.
Convert Date to Month and Year in Excel (TEXT)
Let’s say you need to have the result in a separate cell; in this case, you can use the function TEXT.
The TEXT function helps you change how a date looks by applying a specific format to it and get it back in different cell. For example, you can take a date like “5/8/2024” and display it as “May-2024”.
=TEXT(A1,"mmm-yyyy")
With text function, you have two arguments to define. In the first argument (value), specify the date you want to convert to a month and year or a month and a year separately.
And in the second argument (format_text), you need to specify the format. You can use the formats below, which are the same as those we used in the first methods.
- mmm
- mm
- mmmm
- yy
- yyyy
- mmm-yyyy
- mm-yy
As you can see, when you use “mmm-yyyy” as the format_text in the TEXT function, Excel converts a date into this month-year format, making it easy to read.
Let’s consider a situation where you have day, month, and year as numbers in separate cells. You first need to mine all these to create a date and then get the month and year from it. For this, you can use the below formula:
=TEXT(DATE(YEAR(A1), MONTH(A1), 1), "mmmm yyyy")
And if you need a more textual way to show the month and years in a cell.
=TEXT(DATE(YEAR(A1), MONTH(A1), 1), "mmmm yyyy") ="The month and year are January 2023"
Use Month & Year Functions for Month-Year Formula
You can also write a formula by combining YEAR and MONTH functions.
YEAR extracts the year from a date. For example, if you specify a date like “5/8/2024” into it, it will return “2024”. And, the MONTH returns the month number from a date. For example, if the date is “5/8/2024”, the function will return “5” because May is the year’s fifth month.
Now to write formula with these two function to get month and year together, you can use a hyphen in between:
=MONTH("5/8/2024") & "-" & YEAR("5/8/2024")
=5-2024 When you enter this formula in a cell, it returns a number for the month, a hyphen, and a year number.
Use Custom Formatting to Convert Date into a Month, Year, or Month and Year
Custom formatting allows you to change how dates are displayed in your cells without changing the actual date. You can format a date to show only the month and year, only month, and only year. You can use the below steps for this:
- First, select the cell where you have the date.
- After that, use the shortcut Ctrl + 1 to open the Format Cell option.
- From here, click on the “Custom” category.
- Now, in the type input bar, you must specify the format to apply to the date. For example, you can enter “mmm-yyyy” for Month and Year.
- In the end, click OK to apply the format to the cell.
And when you click OK, it will convert the date into month and year only.
And if you see the formula bar, it’s still a date with day, month, and year. The only difference is the format you have applied to the cell to convert it into month and year.
Below are the few formats that you can use:
- mmm – for the short month name.
- mm – for the month number.
- mmmm – for the long month name.
- yy- for the two-digit year.
- yyyy – for the four-digit year.
Once you apply custom formatting to a single cell, you can use format painter to apply formatting from one cells to another cell or a range of cells.
Create a Custom Function to using VBA to Get Month and Year
If you are familiar with VBA, you can use the code below to create a custom function in Excel to get the month and year from a date.
Here’s the code.
Function MonthAndYear(cell As Range) As String
Dim inputDate As Date
If IsDate(cell.Value) Then
inputDate = cell.Value
MonthAndYear = Format(inputDate, "mmmm yyyy") ' Formats the date to "Month Year"
Else
MonthAndYear = "Invalid Date" ' Returns this if the cell does not contain a valid date
End If
End Function When you enter this function, replace A1 with the cell where you have the date you want to convert. The function checks if the cell contains a valid date and, if so, returns the date formatted as the full month name followed by the year. If the cell does not contain a valid date, it returns a message that says “Invalid Date”.
Cell Reference Vs. Hard Values
Now, when using dates in formulas, you have two ways to enter them within a formula: one is to enter directly in it, and the second is to refer to a cell where you have the date. Referring to a cell is found to be better for several reasons:
- Using cell references allows you to change the date without editing the formula, making it easier to update or correct dates if necessary.
- Directly entering dates into formulas increases the risk of errors due to typos or incorrect date formats.