How to Change Date Format in Excel

In this tutorial, you will learn how to change the date format in excel completely.

When working with dates in excel, we sometimes want to change how we display those dates in their cells. There are some options that we can choose when it comes to displaying dates in excel. We just need to master the methods to change the date format in excel so we can access those options.

Want to know what are the methods to change date format in excel and implement them for your excel work? Learn from this tutorial until its last part!

Disclaimer: This post may contain affiliate links from which we earn commission from qualifying purchases/actions at no additional cost for you. Learn more

Want to work faster and easier in Excel? Install and use Excel add-ins! Read this article to know the best Excel add-ins to use according to us!

Explanation About Excel Date Format

In excel, a date is actually a number which it displays in a different format. The number that we can turn into a date format in excel starts from 1 to 2958465. That is 1 January 1900 until 31 December 9999 when we convert them into dates in excel.

We can think of the number as the days that our date has from 1 January 1900 (starting from 1 for 1 January 1900). For example, 2 is 2 January 1900, 3 is 3 January 1900, 32 is 1 February 1900, and so on.

For the date format itself, excel can display its dates in several different ways depending on our preference. We can display the day, month, or year units short, long, or not displaying the unit at all. Just use the correct date format so we can display our data in the way we want.

Excel Default Date Format

The default way that excel displays our dates depend on the date and time settings on our computer. There are short and long date default formats on which excel bases its way of displaying dates.

If you want to see the short and long date default formats in your excel, then do this. Right-click on any of your cells and choose Format Cells… or press Ctrl + 1 (Command + 1 in Mac) buttons.

Then, go to the Number tab and choose the Date format there. You can see the default date formats in your excel at the very top of the box on the right. They are the ones which excel marks with an asterisk.

How to Change Default Date Format in Excel

Want to change your default date formats in excel? Then, you should change the date and time settings on your computer.

In Windows, you can do that by accessing the Clock, Language, and Region setting from your Control Panel. In the menu page that shows up, choose Change date, time, or number formats.



In the dialog box that shows up, go to the Format tab. From there, change your default date format by changing the pick in the Format, Short date, and Long date dropdowns. You can see the preview of the date format you choose in the bottom part of the dialog box.



You can also change the date format by clicking the Additional Settings… button in the dialog box. In another dialog box that shows up, go to the Date tab and input the date format you want there.



If you use Mac, click the Apple symbol on the left top of your screen and choose System Preferences…. In the dialog box that shows up, click Language & Region and then click the Advanced… button. Go to the Date tab in another dialog box that shows up and input the date format you want there.



If you want to change the date format without changing your default format, then implement the methods we will discuss next!

There are three methods to change the date format in excel we will discuss. The first one is by using the excel date data format.

How to Change Date Format in Excel 1: Date Data Format

We can change the way our dates display themselves by choosing one of the pre-determined date formats excel provides. How can we choose it? Here are the steps you should implement for that.

1. Highlight the cell where the date you want to change the format is
   
   
   
   
2. Right-click on the cell and click Format Cells…. You can also click the Data Format dropdown button in Home and choose More Number Formats… or press Ctrl + 1 (Command + 1 in Mac) buttons
   
   
   
   
   
   
3. Go to the Number tab in the dialog box that shows up and click Date to highlight it
   
   
   
   
4. Choose the date format you want in the box on the right side of the dialog box
   
   
   
   
5. Click the OK button
   
   
   
   
6. Done!
   
   
   
   

How to Change Date Format in Excel 2: Custom Data Format

You can also change your date format in excel by using the custom data format. If you don’t like the pre-determined date format choices from excel, you can customize the format yourself here.

As with the date data format previously, you need to access the Format Cells dialog box to implement this method.

1. Highlight the cell where the date you want to change the format is
   
   
   
   
2. Right-click on the cell and choose Format Cells…. You can also go to the Home tab, click the Data Format dropdown button, and choose More Number Formats…. Or just press Ctrl + 1 (Command + 1 in Mac) buttons
   
   
   
   
   
   
3. In the dialog box that shows up, go to the Number tab and click Custom to highlight it
   
   
   
   
4. Type the date format you want in the text box on the right. You can represent the day unit with d, month unit with m, and year unit with y in your typing. See the preview of the date format you have typed above the text box
   
   
   
   
5. Click the OK button after you have inputted the date format
   
   
   
   
6. Done!
   
   
   
   

How to Change Date Format in Excel 3: TEXT Formula

Another method you can implement to change your date format in excel is by using the TEXT formula. TEXT is an excel formula you can use to convert data writing format into any format you want.

Here are the steps to use this TEXT formula to change your date format in excel.

1. Type an equal sign ( = ) in the cell where you want to put your date with the preferred format
   
   
   
   
2. Type TEXT (can be with large and small letters) and an open bracket sign after =
   
   
   
   
3. Input the date or the cell coordinate where the date you want to change the format of is. Then, type a comma sign ( , )
   
   
   
   
4. Input the date format you want. Symbolize the day unit with d, the month unit with m, and the year unit with y
   
   
   
   
5. Type a close bracket sign
   
   
   
   
6. Press Enter
7. Done!
   
   
   
   

Why I Cannot Change My Date Format in Excel - Possible Reasons & Solutions

Got some problems when you want to change your date format in excel? Some possible reasons can cause that to happen. However, these are probably the most common reasons why the problem occurs and the solution for each of them.

• Reason: Your date has a text data type
  Solution: Excel won’t change your date format if you use a text date. Make sure your date has a date or number data type before you change its format.
  
  You can check your current date data type by highlighting its cell and see the data format text box in Home. If the text box says “Text”, retype your date to change it into “Number” or “Date”
  
  


• Reason: Excel doesn’t recognize your date as a date
  Solution: You probably input something you think is a date but Excel doesn’t recognize it as such. For this, you may want to change all your date writings into default date format before you change their format separately


• Reason: You type the wrong date format when you use the custom data format or TEXT formula method
  Solution: Make sure you type the d, m, and y units correctly for the date format you desire. You may want to check the date format choices in the custom data format box as a reference

We hope one of the points above can help you to solve your date format conversion problem!

A Quick Way to Change a Date into a Short/Long Date Format in Excel

Want to convert your date display into your current default short/long date format in excel? You can do that easily.



Just highlight the cell containing your date, go to the Home tab, and click the data format dropdown button. In the dropdown list, choose Short Date/Long Date.



Doing that will convert your date format into your default short/long date format!

How to Change Date Format in Excel to a Particular Locale

To change your date format into a particular locale, highlight the date cell and activate the Format Cells dialog box. If you want to change it from the date data format, pick the locale you want in the Language dropdown there.



If you want to change it from the custom data format, input the locale code before you type the date format. You must input the code within square brackets and after a dollar and a dash.

For example, here is the code input if we want an English, United States locale.



We input 409 there because that is the code for the English, United States locale. You can see the list of the locale code here.

In the custom format text box, here is an example of how we write the date format.



Do what we have just discussed correctly and you should get your date format in the locale you want!

How to Change Date Format in Excel to Include Time

Want to include time in your date display? You can do that using the Format Cells dialog box too.

For the steps, first, as usual, highlight your date cell and activate the Format Cells dialog box. In the Number tab there, choose Date or Time. You can choose the date and time display format you want from the box on the right.



If you don’t like any pre-determined date and time format from excel, you can also use the custom data format. Type the date and time format you want in the custom data format text box. For time, h represents the hour unit, m represents the minute unit, and s represents the second unit.



Here is an example of the date and time format we can type in the custom data format text box.



Click the OK button in the dialog box after you have got your date and time format. Now, your date should have time too in its display! You can change the time value by editing it directly in the date cell.

How to Convert Date to Number in Excel

Need to convert your date into its numerical value in excel? There are generally two methods to do it.

If you want to convert your date directly in its cell, just change its data type into general. Highlight the date cell, go to the Home tab, and click the data format dropdown button there. Then, choose General from the dropdown list.



You will immediately display the number value of your date in the cell instead of the date itself!



If you want to convert your date to number in another cell, you can use the DATEVALUE formula. However, DATEVALUE only converts your date into its number value if your date has a text data type.

Therefore, apply TEXT on your date before you apply DATEVALUE to it. You can use any date format in your TEXT as you only use its result to get your date number. However, make sure the date format has all the d, m, and y units so you can get your result correctly.



Enter after you type your formula and you will get your date number value!

Exercise

After you have learned how to change the date format in excel completely, let’s do an exercise to deepen your understanding!

Download the exercise file and do all the instructions. Download the answer key file if you have done the exercise and want to check your answers. Or probably when you are confused about how to do the exercise!

Link to the exercise file:
Download here

Instructions:
Change the date in the appropriate gray-colored cell!

1. Change the date into this: 8-Aug-08
2. Change the date into this: 08/08/08
3. Change the date into this: 08 Aug 2008

Link to the answer key file:
Download here

Additional Note

If you change your date into a text data type, you cannot calculate it.



Related tutorials you should learn from: