Excel is a great tool for managing data, whether it be financial or scientific data or any other form of analysis.
However, sometimes the specific data we need isn’t available in the exact format that we need.
For instance, let’s say you have a dataset that includes dates with year information included, but all you actually want to do is remove the year from each date and leave only the month and day.
Excel has a lot of features and formulas that will quickly remove the year from a date in a cell
In this blog post, we will walk you through three different techniques to remove the year from the date in Excel using simple steps.
Method 1 – Remove Year from Date by Using TEXT Function
The TEXT function is an effective method for quickly removing years from dates.
It lets you change the way a number/date appears by specifying the format in which you want that date to be displayed.
Below is the syntax that would take a date as input and removes the year from the date, and only show the month and day values
Here, the selected cell will be the date you want to change from ‘mm/dd/yyyy’ to ‘mm/dd’ format.
Let me show you how this works by taking a simple example.
Below I have a data set where I have some dates in column A, and I want to remove the year value from these dates.
In this case, you can type the formula for removing the year in front of the first date:
After entering the formula, press the Enter key, and the year will be removed for the selected date.
To replicate the result, simply select the cell that has the date without the year and drag the Fill Handle over the cells that you want to contain this formula.
With this, all the years will be removed from all the dates.
In the above formula, we have used the TEXT function that takes the date in cells in column A as the first argument, and then specifies the format in which it should be displayed as the second argument (which is “mm/dd”).
Here, “mm/dd” means that the date would be shown with the month value followed by the date value with a / in between.
Note: The result of the TEXT formula is a text string, which means that you would not be able to use these dates in calculations as numbers.
Method 2 – Remove Year from Date by Using CONCATENATE Function
The CONCATENATE function is usually used to join two or more strings together in a single cell.
You can also use this function to remove the year from the dates.
Below I have a data set where I have dates in column A from which I want to remove the year value
Below is the concatenate formula that would remove the year value from the date in column A.
Once you have carefully entered the formula in the cell, press the Enter key. This will give you the date without year for the selected cell (A2).
Now, you can select the date without the year cell and drag the Fill Handle to the cell where you want to apply this formula.
With this, all years will be removed from all dates in one swift motion.
If your Excel sheet contains dates written using a slash (mm/dd/yyyy), you will need to replace “-” in the formula with a slash, “/”.
Note: You can customize result of the CONCATENATE formula as per your requirement by switching the positions of months and days. For example, if you are looking for dates in “dd/mm” format, then you can modify CONCATENATE function =CONCATENATE(DAY(selected cell),”-“,MONTH(selected cell)).
Just like the result of the TEXT formula (covered in the previous section), the result that you get using the concatenate formula is going to be a text string.
This means that you cannot use these in formulas that require your date to be a number in the back end.
Method 3 – Remove Year from Date by Using the Number Format Options
If you don’t want to remember any formulas for removing the year from date, then this method is for you.
Number Format options in Excel allow you to change the format of the cell so that the year value is not displayed in only the month and date values are shown.
One good thing about this method is that it only changes the way the value is displayed in the cell, but it does not change the actual value in the back-end Excel.
Below are the steps to use the Number Formatting option to change the date by removing the year value:
Step 1: Select the cell or range of cells containing the date with year information.
Step 2: Then click the “Home” tab.
Step 3: After that, go to the “Custom” option from the “Number” section.
Step 4: Click the small drop-down arrow next to the “Custom” options.
Step 5: After that, you will be presented with multiple number format options. Select the “More Number Formats” option present at the bottom.
Step 6: A dialog box titled “Format Cells” will appear. In the Number group, click on the Custom option in the Category list
Step 7: In the “Custom” option, you will be able to see multiple date format options. From those options, you can look for the “mm/dd” format.
If you are unable to find this format, then you can just type “mm-dd” in the “Type” section.
Step 8: Lastly, click “Ok” to implement the custom format to the selected cells.
Once you have clicked “Ok”, your desired format will be applied to all selected cells, and they will show only month and day without year.
Excel Tip: you can also open the Format Cells dialog box by using the keyboard shortcut Control + 1 (hold the Control key and then press the 1 key)
This will directly take you to step six, and you can follow the rest of the steps as stated.
In this article, we have covered three different methods you can use to quickly remove the year value from the date in Excel.
The first two methods use formulas such as TEXT and CONCATENATE, which would give you the result as a text string.
The third method uses the Number Formatting option that would change the format of the cells so that the year value is not displayed while the month and day values are shown.
Choose a method that you think is simple. It does require some time to get proficient in the process, but with a little bit of practice, your efficiency in performing this task will get better.
Other Excel articles you may also like: