This post will guide you how to delete or remove year from a given standard date in Excel. How do I remove the year from a date with a formula in Excel 2013/2016/2019/365.

1. Remove Year from a Date

Assuming that you have a list of data in range B1:B5, in which contain date values. And you want only display the month and Day parts. So you need to remove year from cells. How to do it. You can use a formula based on the MONTH function and the DAY function to achieve the result.  Like this:

=MONTH(B1)&"-"&DAY(B1)

Or

=CONCATENATE(MONTH(B1),"-",DAY(B1))

Type one of the above formula into cell C1, and press Enter key. And then drag the AutoFill handle from Cell C1 to C5.

You should notice that the year part has been removed for each date in range B1:B5.

You can also use the TEXT function to create a formula to achieve the same result. Like this:

=TEXT(B1,"mm-dd")

Type one of the above formula into cell D1, and press Enter key. And then drag the AutoFill handle from Cell D1 to D5.

Note: Both methods will display the result as text, not as an actual date. If you want to use the result in further calculations as a date, you may need to convert it back to a date format using the DATEVALUE function or other methods.

2. Video: Remove Year from a Date

This tutorial video will show you how to remove the year from a date in Excel using functions, you can use the TEXT function or the DAY, MONTH functions in combination.

3. SAMPLE FIlES

Below are sample files in Microsoft Excel that you can download for reference if you wish.

4. Related Functions

• Excel MONTH function
  The Excel MONTH function returns the month of a date represented by a serial number. And the month is an integer number from 1 to 12. The syntax of the MONTH function is as below:=MONTH (serial_number)…
• Excel DAY function
  The Excel DAY function returns a day of a date (from 1 to 31).The DAY function is a build-in function in Microsoft Excel and it is categorized as a DATE and TIME Function.The syntax of the DAY function is as below:= DAY (date_value)…
• Excel Text function
  The Excel TEXT function converts a numeric value into text string with a specified format. The TEXT function is a build-in function in Microsoft Excel and it is categorized as a Text Function. The syntax of the TEXT function is as below: = TEXT (value, Format code)…