Microsoft Excel Guide: Adjusting Dates by Removing a Year

Quick Links

• Custom Number Formatting
• CONCATENATE Function

There are a couple of ways you can remove the year from a date shown in Microsoft Excel. You can use custom cell formatting to hide it or use various functions like CONCATENATE to remove it completely. Here’s how.

Custom Number Formatting

While a date value in an Excel cell is technically a number, Microsoft Excel uses a particular type of formatting to display it. The same is true 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 that it recognizes, Excel automatically changes the cell number format to the “Date” type.

You can customize this type to display the date value in different ways. For instance, you can switch between “11/02/2021” (in the DD/MM/YYYY format commonly used in the U.K.) and “2021-02-11” for 11 February 2021.

Click here

You can customize this number format, however, to remove the year entirely from view. The value itself won’t change, but Excel won’t show the year value thanks to custom number formatting.

To do this, highlight the cells containing your original date values, then select the “Home” tab on the ribbon bar. From there, select the arrow next to the number format drop-down menu and choose the “More Number Formats” option.

In the “Format Cells” menu, select a style of formatting that you like from the “Date” options provided. Once that’s selected, choose the “Custom” option.

In the “Custom” option, you’ll see the date number formatting style shown as text in the “Type” box. For instance, “11/02/2021” would show as “dd/mm/yyyy” in this box.

To remove the year, make sure to remove any references to “yyyy” or “yy” in the “Type” box as well as any surplus delimiters, such as a dash or forward slash. For instance, “11/02/2021” would require you to remove “/yyyy” from the “dd/mm/yyyy” type box, leaving “dd/mm” in place.

Once you’ve made your changes, select the “OK” button.

Changing the cell number type will hide the year, but it won’t remove it. If you want to restore the year, change your cell number type to a date type that includes the year again.

CONCATENATE Function

Concatenation is a term that basically means to link or combine two or more pieces of data. In Microsoft Excel terms, concatenation can be used to add various text strings, numbers, or cell values together in a new cell.

If you want to remove the year from a date in Excel, you could combine the output from two functions (such as DAY or MONTH) together using CONCATENATE. Rather than hiding the year from view using custom number formatting, CONCATENATE (with DAY and MONTH) allows you to create a separate value that doesn’t include the year at all.

To do this, open an Excel workbook containing your date values, or create a new workbook and place date values in separate cells. To use CONCATENATE with DAY and MONTH, insert a new function using this structure, replacing the cell reference (A2) with a reference to the cell containing your date:

=CONCATENATE(DAY(A2),”/“,MONTH(A2))

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

In this example, cell A2 contains a date value (11/12/2021) in the DD/MM/YYYY format. Using CONCATENATE with DAY and MONTH, the day (11) and month (12) from the date value in A2 are placed in another cell separated by the delimiter, which, in this case, is a forward slash.

You can customize this formula to change the order of the day and month values (eg. MM/DD) or to use a different delimiter character. You can also use the fill handle to copy this formula into other cells in your column to remove the year from multiple date values.

Related: How to Automatically Fill Sequential Data into Excel with the Fill Handle

Also read: