Dates in Microsoft Excel are useful for tracking finances, orders, and sales. So, there may come a time when you want to pull a month or year out of a date entry. Functions and formulas make this an easy task.
There are a couple of ways to get a month from a date in Excel, depending on if you want to display the result as a number, word, or abbreviation. And luckily, you can use these same methods to get the year from your date entry.
Get the Month or Year With Date Functions
If you want to get the numeric value for a month such as 10 for October, 11 for November, and so on, the MONTH function gets the job done quickly. For years, you can simply use the YEAR function and a reference.
The syntax for each function is the same:
YEAR(reference) where you refer to the cell containing the date.
You can use the following formulas to get the month and then the year from the date in cell A2:
You’ll then see the result in the cell containing the formula.
Remember, the month is formatted as its numeric value.
If you have a list of dates where you want to grab the month and/or year for each entry, use the fill handle to drag the formula(s) down to the remaining cells.
Get the Month or Year With the TEXT Function
Maybe you prefer to see the name of the month or an abbreviation rather than the number. You can do this for the month using the TEXT function. You can also get the two-digit number for the year with this method.
The syntax for the function is
TEXT(value, format_code) where you’ll need both arguments to display the month. The
value is the cell containing the date and the
format_code is how you want to display the result.
Here, we’ll get the month for the date in cell A2 as a full word using the letter M for month as the
Note, you need at least four M’s within quotation marks to get the full month name. The number of letters in the month’s name does not correspond to the number of M’s in the argument; just enter at least four of them.
To get the three-letter abbreviation for a month instead of the full name, just use three M’s:
You can also use the TEXT function if you only want two digits for the year rather than all four. You’ll use the letter Y for year as the
Again, you can use the fill handle to copy the formula(s) down to your remaining cells if you like.
Pulling a month or year out of a full date in Excel takes only a minute with these methods. For more, check out these additional date and time functions in Excel you might find useful.
- › How to Batch Edit Photos and Videos on iPhone
- › How to Change Apple ID on iPhone
- › How to Use Your Apple Watch’s Hidden Web Browser (and Why You Shouldn’t)
- › How to Change Your Hotspot Name on iPhone and Android
- › How to Test Your Computer’s PSU With a PSU Tester
- › How to Remove Dotted Lines in Microsoft Excel