How to Delete or Remove Year from a Date in Excel

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.

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.

delete year from date1

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.

delete year from date2

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)…

You might also like:

Comments

So empty here ... leave a comment!

Leave a Reply

Your email address will not be published. Required fields are marked *

Sidebar