How to Remove Year from Date in Excel (With Examples)


You can use either of the following formulas to remove a year from a date in Excel:

Formula 1: Use TEXT Function

=TEXT(A2, "m/d")

Formula 2: Use CONCATENATE Function

=CONCATENATE(MONTH(A2),"/",DAY(A2))

Both formulas remove the year from the date in cell A2.

The following examples show how to use each formula in practice with the following list of dates in Excel:

Example 1: Use TEXT Function to Remove Year from Date

We can type the following formula into cell B2 to remove the year from the date in cell A2:

=TEXT(A2, "m/d")

We can then click and drag this formula down to each remaining cell in column B:

Excel remove year from date

Column B now displays each of the dates in column A with the year removed.

This formula works by using the TEXT function in Excel to convert the dates in column A to a text format with m representing the month and d representing the day.

By using this formula, we’re able to display the date values without the year.

Note that you could also use the following formula if you’d like the months and days to both be shown with two decimals:

=TEXT(A2, "mm/dd")

For example, this would display the date 1/5/2023 as 01/05.

Example 2: Use CONCATENATE Function to Remove Year from Date

We can type the following formula into cell B2 to remove the year from the date in cell A2:

=CONCATENATE(MONTH(A2),"/",DAY(A2))

We can then click and drag this formula down to each remaining cell in column B:

Column B now displays each of the dates in column A with the year removed.

This formula works by using the CONCATENATE function in Excel to simply concatenate the month and day values together from the dates in column A with a slash as a delimiter.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

How to Add Years to Date in Excel
How to Calculate Years Between Two Dates in Excel
How to Convert Date to Month and Year Format in Excel

Featured Posts

Leave a Reply

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