How to remove a year from a date in Microsoft Excel

There are several ways to remove the year from a date displayed in Microsoft Excel. You can use the custom cell formatting to hide it or you can use various functions like CONCATENATE to remove it completely. Here is how.

Custom number formatting

While a date value in at Excel Cell is technically a number, Microsoft Excel uses a certain type of formatting to display it. The same goes for currency values, where currency symbols are added to values ​​to show that they relate to money. When you add a date in Excel in a format it recognizes Excel automatically changes the cell number format to type “date”.

You can customize this type to display the date value in different ways. For example, for February 11, 2021, you can toggle between “02/11/2021” (in the UK / MM / DD / YYYY format) and “2021-02-11”.

However, you can customize this number format to completely remove the year from the display. The value itself won’t change, however Excel does not show the year value thanks to user-defined number formatting.

To do this, highlight the cells that contain your original date values, then select the tab “begin” in the ribbon. From there, select the arrow next to the number format drop-down menu and select the option “Other number formats”.

Number Format> More Number Formats.” width =”650″ height =”477″ src =”/pagespeed_static/1.JiBnMqyl6S.gif” onload =”pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this);” onerror =”this.onerror = null; pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this);”>

Select in the menu “Format cells” a formatting style you like from the ones provided “date”Options. Once that’s selected, choose the option “Custom”.

Select in the menu

In the option “Custom” becomes the formatting style for date numbers as text in the field “Type” displayed. For example, “11/02/2021” would appear in this field as “DD / MM / YYYY”.

To remove the year, remove all references to “yyyy” or “yy” in the “Type” field as well as any superfluous separators such as hyphens or slashes. For example, for “02.11.2021” you have to remove “/ yyyy” from the “dd / mm / yyyy” field and leave “dd / mm” unchanged.

After you have made your changes, select the “OK” button.

Remove “yy” or “yyyy” from the “Type” field and confirm with “OK”.

When you change the cell number type, the year is hidden but not removed. If you want to restore the year, change your cell number type to a date type that includes the year again.


Concatenation is a term that essentially means linking or combining two or more pieces of data. At Microsoft Excel In terms of terms, concatenation can be used to combine different text strings, numbers, or cell values ​​in a new cell.

A example different text strings combined with the Excel CONCATENATE function.

When you want to remove the year from a date in Excel, you can combine the output of two functions (like DAY or MONTH) with CONCATENATE. Instead of hiding the year with custom number formatting, you can use CONCATENATE (using DAY and MONTH) to create a separate value that doesn’t include the year at all.

To do this, open a Excel Workbook that contains your dates or create a new workbook and put dates in separate cells. To use CONCATENATE with DAY and MONTH, add a new function with this structure and replace the cell reference (A2) with a reference to the cell that contains your date:


The values ​​returned by DAY and MONTH are separated by a special separator, such as a comma or a slash, to indicate that the value returned is a date. Each value (DAY, the separator and MONTH) is separated by a comma in the CONCATENATE function.

In this example, Cell A2 contains a date value (12/11/2021) in the format DD / MM / YYYY. When CONCATENATE is used with DAY and MONTH, the day (11) and month (12) from the date value in A2 are inserted into another cell separated by the separator, which in this case is a slash.

Examples of the Excel CONCATENATE function for removing the year from date values.

You can adjust this formula to change the order of the day and month values ​​(e.g. MM / DD) or to use a different separator. You can also use the fill handle to copy this formula to other cells in your column to remove the year from multiple date values.

How to automatically fill in sequential data Excel with the filling handle

Related Posts