Share via

How do I get Excel 2019 to convert a date to only the year

Anonymous
2024-01-24T16:27:14+00:00

I have an excel spreadsheet containing a pivot table. One data column shows a date in the format dd/mm/yyyy and this column is used in a pivot table where only the year is required to be shown.

Until recently the year only appeared in the columns of the pivot table but now, when I click on the drop down for the years the drop down menu shows every year month not just the year. This makes it very difficult to change the years to be shown as there are twelve months/years to be selected or de-selected each time and instead of having only one column for each year I now have 12.

Is there anyway to get a formula in the source data worksheet that will extract only the year from the original dates. I have attempted it by using "yyyy" as the formating but still the month and year format is appearing in the pivot table.

I have had similar problems in the past when dealing with dates in Excel. Whilst the date function is normaly sufficient for most purposes I have often had occassion to require only the day, month or year to be extracted from a date. If I use the formula =year(a1) I do not get the year value of the date but 1905 which is the start year used by Excel for the date number.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

13 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Rich~M 20,370 Reputation points Volunteer Moderator
    2024-01-24T20:27:54+00:00

    Hi SouthEssex. I an an Excel user like you.

    Here is a formula that will give a text expression of the year as long as there is an actual date to pull it from in A1.

    =TEXT(A1,"yyyy")

    100+ people found this answer helpful.
    0 comments
  2. Rich~M 20,370 Reputation points Volunteer Moderator
    2024-01-24T21:05:21+00:00

    Additionally, if you need it to be formatted as a number so that Excel can sort them in order as numbers you can use this adding the NUMBERVALUE function.

    =NUMBERVALUE(TEXT(A1,"yyyy"))

    10+ people found this answer helpful.
    0 comments
  3. Anonymous
    2024-01-27T11:17:57+00:00

    Thanks for your suggestion. I tried it and for a source date of 16th November 2015 I got a result of 1905.

    6 people found this answer helpful.
    0 comments
  4. Anonymous
    2024-01-24T20:24:38+00:00

    Hi SouthEssex

    I'm AnnaThomas and I'd happily help you with your question. In this Forum, we are Microsoft consumers just like yourself no one here is a Microsoft employee.

    If the YEAR function is not working as expected, you can try an alternative using DATEVALUE. For example, you can use the formula =YEAR(DATEVALUE(A1)).

    I hope this helps ;-), let me know if this is contrary to what you need, I would still be helpful to answer more of your questions.

    Best Regards,

    AnnaThomas

    Give back to the community. Help the next person with this problem by indicating whether this answer solved your problem. Click Yes or No at the bottom.

    5 people found this answer helpful.
    0 comments
  5. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2024-01-27T23:48:04+00:00

    Hi,

    Use the Group feature of a Pivot Table.

    2 people found this answer helpful.
    0 comments