Remove or hide year from date in Excel – 3 quick ways
Removing the year from a date in Excel can be useful for several reasons. For instance, if you want to show only the month and day for annual events, create summaries where the year isn't needed, or keep certain details private, like hiding the year of birth in a list of employee birthdays. Here’s a detailed guide on how to effectively remove the year from a date in Excel, ensuring that your data is displayed exactly as you need it.
Format date to only display the month and day
In Excel, if you only need to modify how a date is displayed—showing just the month and day—you can change the date formatting without altering the actual value. This adjusts only the visual representation of the date while keeping the original data intact. This approach is especially beneficial when you require a quick and temporary change in how the date appears, ensuring that the underlying data remains unchanged.
In this case, here, I will introduce two methods for solving this task.
Format date to only display the month and day by using Format Cells feature
- Select the range of cells containing the dates.
- Right-click on the selection, choose Format Cells, see screenshot:
- In the Format Cells window, navigate to the Number tab, select Custom from the categories on the left, and type MM-DD or DD-MM in the Type box, depending on your preference for month/day order. See screenshot:
- Then, click OK to apply the format. This changes the display format without altering the underlying date values, which still retain the year component. See screenshot:
Format date to only display the month and day by using Kutools for Excel
Kutools for Excel's Apply Date Formatting feature offers over 40 predefined date formats, plus the flexibility to create custom formats tailored to your specific needs. This robust tool enhances your spreadsheet management by allowing you to precisely control how dates are displayed, ensuring your data presentation aligns perfectly with your requirements.
After installing Kutools for Excel, click Kutools > Format > Apply Date Formatting, in the Apply Date Formatting dialog box, choose the specific month and day format you need, then, click OK button. See screenshot:
Result:
Now, you can see that the dates are displayed as month and day. This display format modifies how the dates appear without changing the underlying date values, which still include the year component.
Remove year from date with formula
If you want to completely remove the year from a date, you can achieve this by using a formula. This method ensures that the year is permanently deleted, leaving only the month and day in the processed date.
1. In the adjacent blank cell, type the following formula:
=TEXT(B2,"mm-dd")2. Then, drag the fill handle down to apply the formula to other cells in the column. And this formula converts the date into text format showing only the month and day. See screenshot:
Convert to Values: If you need static text rather than a formula, copy the new column and use Paste Special > Value under the Home tab to paste as values.
Related Articles:
- Remove time from date / timestamp in Excel
- When working with date-time data in Excel, you may encounter datetime values like "1/24/2024 14:30:00," and you might need to extract only the date "1/24/2024" without the time part. This article will introduce you to four simple methods to remove the time from the timestamp. These methods are simple and easy to understand, suitable for various situations, and whether you are a beginner or an experienced Excel user, you can quickly learn and apply them.
- Change American date format in Excel
- Different country has different date format. Maybe you are a staff of a multinational corporation in America, and you may receive some spreadsheets from China or other countries, you find there are some date formats that you are not accustomed to using it. What should you do? Today, I will introduce you some ways to solve it by changing other date formats to American date formats in Excel.
- Calculate number of days in a month or a year
- As we all know, there are leap years and common years where leap year has 366 days and common year has 365 days. To calculate the number of days in a month or a year based on a date as below screenshot shown, this article will help you..
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
