How to Remove Year from Date in Excel (5 Easy Ways)

There are a couple of ways to remove year from date in Excel. Functions such as TEXT; offer a format to enter, MONTH and DAY; extract month and day from a date. General Date Format, Custom Date Format, and Text to Column features also remove year from the date in Excel. In this article, we use those above-mentioned functions and features to remove the year from the date in Excel.

Let’s say we have Sales data Date of Products in a year. Now, we want the data containing only month and day removing the year.

Dataset-How to Remove Year from Date in Excel


Download Excel Workbook


5 Easy Ways to Remove Year from Date in Excel

Method 1: Using TEXT Function to Remove Year from a Date

The TEXT function converts numbers to text strings in a given format. As we want to remove the year portion from a date, we can use the TEXT function to display dates without the year. The syntax of the TEXT function is

Text(value, format_text)

The arguments,

value; numeric value is to be converted into text.

Format_text; desired format to display the text.

Step 1: Paste the following formula in any adjacent cell (i.e., E5).

=TEXT(B5,"mm/dd")

In the formula,

B5; the value.

“mm/dd”; the format_text.

Text function-How to Remove Year from Date in Excel

 Step 2: Press ENTER and Drag the Fill Handle to display just the month and day values in other cells.

Text function result

From the above screenshot, we can see only the month and day values are in the new column. You can input any format in the format_text argument to display the dates.


Method 2: Using MONTH and DAY Function to Remove Year from Date in Excel

Excel’s MONTH and DAY functions extract the month and day values respectively from a given date. Since we have dates, we can fetch the month and day numbers from the dates using the MONTH and DAY function. After that join the month and day values with the CONCATENATE function or Ampersand. The syntax of the MONTH and DAY functions are

MONTH (serial_number)
DAY (date)

The arguments in the syntaxes are,

serial_number; any valid date.

date; any valid date.

Step 1: Type the below formula in any blank cell (i.e., E5).

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

The formula first fetches the month and day value from a valid date (i.e., B5). Then joins the month and day values with a delimiter (i.e., ).

month and day function-How to Remove Year from Date in Excel

Step 2: Hit ENTER then drag the Fill Handle to appear the dates without the year portion as shown in the picture below.

month and day function result

You can use any delimiter to separate the month and day values.


Method 3: Using Date Format to Remove Year from Date in Excel

Excel offers multiple Date Category types in the Format Cells dialog box. We can select multiple date Types as our data demand. To remove the year portion from a date, we can use one of the offered date-type formats.

Step 1: Select a range of dates from where you want to remove the year. Then, Go to the Home tab > Click on the Font Setting icon (shown in the below image). It brings the Format Cells dialog box.

Date format-How to Remove Year from Date in Excel

Step 2: On the Format Cells dialog box; In the Number section,

Select Date as Category.

Select date Type as day-month or month-day (i.e., 14-Mar or 3/14).

Click OK.

Format Cells


There are alternative ways to bring out the Format Cells dialog box such as a) Keyboard Shortcuts (CTRL+1), and b) Number Section.

 a) Keyboard Shortcuts (CTRL+1)

➤ After selecting the range press CTRL+1 altogether to bring out the Format Cells dialog box.

Format Cells


 b) Number Section

➤ Again, after selecting the range, Go to the Home tab > Number section > Select More Number Formats to bring the Format Cells dialog box.

Number section

➤ The Format Cells dialog box appears.

Format Cells


In the Format Cells dialog box, clicking OK leads to removing all the year portions from dates in the cells as depicted in the following picture.

Date format result-How to Remove Year from Date in Excel


Similar Readings


Method 4: Using Custom Category in Format Cells

From the earlier method (i.e., Method 3), we know Excel offers multiple date types and we use one of them to remove year from dates. However, there is another option in the Category section named Custom in the Format Cell dialog box.

Step 1: Follow any of the ways described in Step 2 of Method 3 to make the Format Cells dialog box appear.

Type the colored text under the Type section of the Format Cells dialog box to display the dates in the format without the year- mm/dd;@

You can use any month-day format to display the outcomes.

Then, Click OK.

custom category-How to Remove Year from Date in Excel

Execution of Step 1 returns the resultant values similar to the image below.

custom category result-How to Remove Year from Date in Excel


Method 5: Using Text to Column Feature to Separate Month and Day Portions

Excel’s Text to Column feature separates portions of entries by delimiters. We have dates separated by the slash (/) delimiter in the dataset. We can command Excel using the Text to Column feature to separate portions of dates by a slash (/) and display them in separate columns.

Step 1: Before commencing to apply the Text to Column feature, Insert a column named Day beside the Order Date column.

Column insertion

Step 2: Pick the range then Go to the Data tab > Select Text to Column (from the Data Tools section).

text to column-How to Remove Year from Date in Excel

Step 3: The Convert Text to Columns Wizards- Step 1 of 3 window appears. In the window,

Mark Delimiter as Choose the file type that best describes your data option.

Click Next.

Convert text to column

Step 4: Next the Convert Text to Columns Wizards- Step 2 of 3 appears. In the window,

Type Slash as Other Delimiters.

Click on Next.

Convert text to column

Step 5: At the end, Convert Text to Columns Wizards- Step 3 of 3 window appears. Inside the window,

Mark the Do not import column (skip) option.

Select the Year portion as depicted in the following screenshot.

Click on Finish.

Convert text to column window

Step 6: A warning appears saying whether you want to replace the data or not.

Click OK.

Warning window

As columns (i.e., Order Date and Day) are in date format, the outcomes of the executed steps will be the same as shown in the picture below.

text to column result

Step 7: Rename the column Order Date as Month then Choose the General as number Category for both Month and Day column in the Format Cells window.

Click OK.

general category

Selecting General Category in the Number section converts the date values to numbers.

general format values

Step 8: Clicking OK in Step 7 transforms all the values in general format. After getting the month and day values, Paste the following formula to construct a date with the year in any cell (i.e., D5).

=C5&"-"&D5

The ampersand joins the month and day values with a delimiter.

Formula insertion

Step 9: Press ENTER and Drag the Fill Handle to display dates containing only month and day values as shown in the screenshot below.

Fill Handle

You can make the outcomes look like the following image.

Final result

To ease your understanding, we’ve brought back the original date column, hope you can relate to this better now.

Read More: How to Delete Empty Rows and Columns in Excel VBA (4 Methods)


Conclusion

The TEXT function displays a given value in formatted text and combined MONTH and DAY functions can also fetch month and day values from a date. In this article, we also demonstrated multiple features to remove year from the date in Excel. Though the Text to Column feature needs multiple steps to achieve the goal, it depends on your demand. Hope you find these above-mentioned methods to excel in their purpose. Comment if you have further inquiries or have something to add.


Related Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo