Want to show only the year, month, or day from an Excel date? We explain how to use the TEXT(), YEAR(), MONTH(), and DAY() functions or cell formatting to display or calculate date details efficiently. Learn when to use each method, which is fastest, and how your choice affects Excel performance and spreadsheet clarity.
Once you have a full date in Excel using the serial format, there are many ways to slice, dice and present it. Just the year, month, day, day of the week or week number in the year … all in the format of your choice. So many ways that you have to be careful choosing the right one for the job.
Show text or value
For many results there are two ways to show parts of a date in Excel.
Text only – the cell has the full date with cell formatting to show only parts of the date (day, month etc.) in the sheet.
Value – the cell recalculates from the date to a value that can be used in other calculations.
Check out this example. Cell A1 has an Excel serial date saved as 46207 but displayed as a date.
Column B has three displays of the year, based on the date in A1. They appear to be the same result, but have been made in different ways and have varying uses.
B2 is the same value as A1 (e.g. =A1)a full date but the cell formatting has changed to show only the year.
The cell contains an Excel serial date.
B3 also uses the TEXT() function to transform into another look. In this case, the date in A1 into a year format “yyyy”. The “yy” format would show the last two digits only e.g. “26”.
The cell contains text.
B4 uses the YEAR() function to return the year as a value. Use this method to do further calculations (e.g, add or subtract years).
Many date formatting choices
Here’s a selection of date formatting or conversion choices showing the formula and type of result. All sourced from the full date in A1 (top-left).
Both cell formatting and Text() have many more options for the formatting codes.
As well as separating out parts of a date (Day(), Month() or Year(), there are other functions:
Weekday() – gives the ‘day of week’ number e.g. 1 for Monday, 2 for Tuesday. There are options to control the starting day and number.
WeekNum() – the number of the week in the year so far, with options to control the system used and day to start.
ISOWeekNum() – same as WeekNum() but complies with the international standard (ISO).
Which to choose?
If the purpose of the cell result is display only, then use cell formatting.
If the cell will be used for other calculations then use one of the functions() that return a value, not text.
Cell formatting is fastest (see below) but can confuse others working on the same spreadsheet because there’s nothing in the formula to explain why only part of the date is displayed. Sometimes cell formatting is overlooked.
Text() gives the same visible result, using the same formatting codes as cell formatting and is obvious to anyone else working on the sheet.
Functions like Year(), Month() and Day() return values that can be used for other calculations.
Which is faster?
For small or medium sized spreadsheets, the speed of the date conversion options isn’t a concern. However, in a large sheet with many date calculations, the choice can affect Excel’s performance.
Fastest
Custom Cell format – If you only want to show part of the date (e.g., year), format the cell as yyyy, mmm, dddd, etc. Zero calc cost.
Fast
YEAR()/DAY()/MONTH() – Return numbers via simple arithmetic on Excel’s date serial—cheap to calculate, spill/sort/filter-friendly. Crucially, they are numeric results available for other calculations.
Slowest
TEXT(date, “yyyy”): Building a text string is more work per cell. Results are text (not numbers), so sorting, filtering, or math need extra conversions.
Excel Users: Are Your Date Calculations Accurate?
Mastering Date Comparisons in Excel Using IF Statements
Six ways to put the date and time into Excel
Text to Excel Date conversion by adding Zero with Paste Special
