How to Change Date Format in Microsoft Excel

In this article, we will learn how to customize the date formatting. In Excel date format changed through custom formatting.

Custom Format: -We can use Custom Number Format to display a Number, date as per our requirement. It’s a tool from the old version of excel when there was no Conditional Formatting option in Excel. All conditional options are performed by using Custom Number Format.

To customize the date formatting, we understand the type of format and role.

In below-given table, we can see the types of formatting of date: -

Let’s take an example to understand how we can customize the date formatting.

Firstly, we will learn how the each date formatting performs in excel.

Day, Month, and Year formatting (d/m/yy)

Cell A1 contains a date. We use the each date format one by one.
 

 
To day display without 0 if the number is lower than 10 (D), Month display without 0 if the number is lower than 10, and show the last 2 digit of year, follow below given steps.

• Press the key Ctrl+1.
• Format cells dialog box will appear.
• In the number tab, select custom from the category list.
• Type d-m-yy in the type box.
• Click on OK.

• The date formatting will get change in day without 0, month without 0 and years with last 2 digit of the years.

To day display with 0 if the number is lower than 10 (D), Month display with 0 if the number is lower than 10, and show the last 2 digit of year, follow below given steps.

• Press the key Ctrl+1.
• Format cells dialog box will appear.
• In the number tab, select custom from the category list.
• Type dd-mm-yy in the type box.
• Click on OK.

• The date formatting will get change in day with 0, month with 0 and years with last 2 digit of the years.

 

 
To day display with the first 3 characters of day, month displaywith the first 3 characters of month, and show the last 4 digit of year, follow below given steps.

• Press the key Ctrl+1.
• Format cells dialog box will appear.
• In the number tab, select custom from the category list.
• Type ddd-mmm-yyyy in the type box.
• Click on OK.

• The date formatting will get change in day with the first 3 characters of day, Month display with the first 3 characters of month, and show the last 4 digit of year.

 

 
To the day display with the full characters of the day, month display with the full characters of the month, and to show the last 4 digit of the year, follow below given steps.

• Press the key Ctrl+1.
• Format cells dialog box will appear.
• In the number tab, select custom from the category list.
• Type dddd-mmmm-yyyy in the type box.
• Click on OK.

• The date formatting will get change in day with the full characters of day, month display with the full characters of month, and show the last 4 digit of year.

 

 
These are the ways to customize the date formatting in different ways by using the custom format in Microsoft Excel.

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com