Share this article
Improve this guide
Excel YEAR Function Not Working: 9 Best Fixes
8 min. read
Published onOctober 7, 2024
published onOctober 7, 2024
Share this article
Improve this guide
Read our disclosure page to find out how can you help Windows Report sustain the editorial teamRead more
How can you fix the Excel YEAR function when it’s not working?
It’s one of the easiest ways to extract a year component from a long or short date inMicrosoft Excel. However, this function is prone to many errors especially if the source data value isn’t in the right Date format.
So, I’ve recreated some Excel YEAR function issues that you may face. Here are the fixes that worked out for me the best!
How To Fix Excel YEAR Function Not Working
Before you start troubleshooting, ensure you’re using a supported Windows, Mac, or web app. The function is only available inExcel 2016 for Windows and Macand newer, andthe web version. If you’re using any earlier desktop editions, unfortunately, it won’t work.
Choose the Right Function Syntax
The most common mistake is not spelling the function syntax correctly. This causes the entire formula to break down. The Excel warning you get in this situation is the#NAME?error.
To avoid spelling issues, you must use the function suggestion as shown on the cell when you type initials after the equals sign.
You can scroll down or up to find the one you need and simplydouble-clickto add italong with the opening parenthesis.
You can now type the rest of the formula arguments. Refer to the contextual suggestions for the valid arguments just below the cell where you’re typing to avoid flaws in argument construction.
Value Isn’t in the Date Format
Here are the possible slip-ups you might make while using the YEAR function:
For example, this function has broken down because there’s a tilde sign in the referenced cell.
When I remove the accent sign, it works just fine.
Custom Date Formats
Another pressing problem with YEAR is its inability to appropriately detect the year value from a custom-formatted date. For example, it’ll work with popular date conventions, likeYYYY/MM/DDorMM/DD/YYYY.
However, when you place the function with aDD/MM/YYYYorYYYY/DD/MMformat, it fails to isolate the year.
Another way to get the same error is by using theCustomformatting category from theFormat Cellsdialog box.
If you select a cell and press theCtrl+1keys, theFormat Cellsdialog box will show up. Now, go toCustomand enter any of the following date formats in theTypefield:
If you now type any date in thecustom-formatted cell, you’ll get an error for the YEAR function.
So, avoid the above custom date formatting instances.
Date Entries Formatted as Numbers
When you convert a date entry to theNumberformatting style, the YEAR function shows an error.
Look at the screenshot below. When it’s in its correct form, like theDateformat, the function can properly extract the year in cellC1.
However, changing the date to aNumberformat shows the#REF!error message.
Another scenario when you might experience the same issue is deleting the old entry and typing in a new value.
When you delete the previous entry inA1, Excel hard codes the YEAR function in the following syntax:
=YEAR(#REF!)
Now, if you update the referenced cell with a new value, Excel can’t detect that because it has automatically added the#REF!error to the YEAR function by replacingA1, which was the blank cell.
You can fix this issue by editing the arguments again. For example, replace#REF!in the formula syntax withA1.
The YEAR function will calculate the year value again.
Hidden Characters or Spaces
Hidden characters, likenon-breaking spaces, change the appropriate formatting of date entries. When you refer your YEAR function to any such cells, you’ll get a#VALUE!error.
To clear these unwanted characters from your dataset, pressAlt+F11to launchExcel VBA Editor.
Right-clickon the current workbook in theleft-side navigation panelof theVBA Editor. Hover the cursor overInsertand selectModulefrom the context menu.
A blank module will open on the right.
There,copy and pastethe following VBA script:
Hit theSavebutton on the toolbar.
Now, select the worksheet dataset from which you wish to remove hidden characters.
Click on theRunbutton on the toolbar or hitF5on the keyboard.
Excel VBA macrowill clean the dataset automatically.
The YEAR function also instantly calculates the cell and shows the year value.
Dates Beyond Excel’s Supported Range
The YEAR function is only effective within a year range of1900and9999. If you refer to an input cell containing a year below or above the given range, the function will either show an error message or an incorrect value.
For example, I entered01/01/1899inA1and used the=YEAR(A1)syntax inC1.
I got the#VALUE!error.
The same error will show up when the input date is01/01/10000.
So, check if the input has a year value outside the given range to fix the issue.
Regional Settings Mismatch
If theregion settings in the PCand Microsoft 365 Admin Center don’t match,Excel might process date formats incorrectly.
First, ensure your PC is set to the desired region configuration by clicking theENGicon in theSystem tray.
English (United States)should be on the top of any other input languages if you’re inthe USor following theUS input format.
If you don’t see theUS option, click on theLanguage preferencesoption.
Click on theAdd a languagebutton below thePreferred languagessection.
Find and selectEnglish (United States). ClickNext.
Now, navigate to yourMicrosoft 365 Admin Centerportal.
TypeRegionin thetop search fieldand selectMy settings and privacy.
You’ll now arrive on theSettings & Privacypage. Navigate to theLanguage & Regiontab on the right.
Now, click on theRegion formatoption and selectChange regional formatbutton.
Select theEnglish (United States)format from the list.
You’ll see a sample date confirming that you’ve successfully selected English (United States) as the region.
Now,restartthe Excel desktop app.
Try using the YEAR function and this time it should be able to extract the year from the date.
Negative or Non-Gregorian Dates
The YEAR function works well withGregorian calendardates. Even if you refer to a non-Gregorian calendar date, it’ll try to show the equivalent year.
For example, the following screenshot shows that the argument is referencing cell A1 which has a date from the Hijri calendar. Therefore, the function can’t extract the year value appropriately and shows the#VALUE!error.
Moreover, if the input date value comes with a minus sign, the YEAR function won’t be able to calculate the year entry because Excel converts the source data into a negative fraction.
So, ensure there are no negative dates in the input dataset.
Use YEARFRAC for Fractional Year Values
If you’re trying to extract the number of years from a start and end date, you’ll get an incorrect result with the YEAR function.
It’ll process it as an input value and derive the corresponding year if possible, but not the actual calculated value.
For instance, if you subtract the end date12/10/2024from the start date6/10/2025in Excel, it returns1643. Now, the YEAR function converts1643to1904instead of4.5years.
Here, you can use the following formula with theYEARFRACfunction to quickly get the calculated year.
In the above formula,A2is the start date andB2is the end date.
Excel will calculate and extract the number of years elapsed, which is4.5.
Summary
Now you know what to do when the Excel YEAR function is not working.
Did any of the above methods work for you? Do you know of a better approach? Share your thoughts below.
More about the topics:Microsoft Excel,Microsoft Office
Selma Citakovic
Content Editor
Selma is a tech fan and editor at Windows Report. She is also an experienced writer with a focus on cybersecurity, troubleshooting hardware and operating systems, gaming, and all things Windows.
User forum
0 messages
Sort by:LatestOldestMost Votes
Comment*
Name*
Email*
Commenting as.Not you?
Save information for future comments
Comment
Δ
Selma Citakovic
Content Editor
Selma is a tech fan and editor at Windows Report. She is also an experienced writer with a focus on cybersecurity and all things Windows related.
