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: -

Description Types Role
Day d Day display without 0 if the number is lower than 10
Day dd Day display with 0 if the number is lower than 10
Day ddd Day display with the first 3 characters of day
Day dddd Day display with the full name of day
Month m Month display without 0 if the number is lower than 10
Month mm Month display with 0 if the number is lower than 10
Month mmm Month display with the first 3 characters of month
Month mmmm Month display with the full name of month
Year yy or y It will show the last 2 digit of year
Year yyy It will show the full year

 

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.
 
img2
 
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.

 
img3
 
img4
 

  • 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.

 
img5
 

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

 
img6
 
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.

 
img7
 

  • 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.

 
img8
 
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.

 
img9
 

  • 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.

 
img10
 
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



4 thoughts on “How to Change Date Format in Microsoft Excel

    • Hi Trupesh,

      You can select the cell where the date is in (03/13) format for example. Right click on the cell and select format cells. In Number Tab —> click on Date —> Select 1-Sep-2014. Click on Ok

      You can also use another way. Right click on the cell and select format cells. In Number Tab —> click on Custom —> In Type write d-mmm-yyyy. Click on Ok

  1. If I have a pdf format and I exported it to excel with a date: 2/11/2015 3:04:27 all in one cell, how do I separate the two to convert the date to: 02/11/2015? I have a long list of names with different dates. I don’t want the time there.

Leave a Reply

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

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>