How to Change Date Format in Excel - Compute Expert

How to Change Date Format in Excel


Home >> Excel Tutorials from Compute Expert >> Excel Tips and Trick >> 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







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 1990 (starting from 1 for 1 January 1990). For example, 2 is 2 January 1990, 3 is 3 January 1990, 32 is 1 February 1990, 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 Date Format in Excel - Screenshot of Where We Can See Default Date Formats in the Format Cells Dialog Box



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.

How to Change Date Format in Excel - Screenshot of the Windows Dialog Box Where We Can Change the Default Date Formats in Excel 1

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.

How to Change Date Format in Excel - Screenshot of the Windows Dialog Box Where We Can Change the Default Date Formats in Excel 2

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.

How to Change Date Format in Excel - Screenshot of the Mac Dialog Box Where We Can Change the Default Date Formats in Excel

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

    How to Change Date Format in Excel - Screenshot of Step 1-1

  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

    How to Change Date Format in Excel - Screenshot of Step 1-2-1

    How to Change Date Format in Excel - Screenshot of Step 1-2-2

  3. Go to the Number tab in the dialog box that shows up and click Date to highlight it

    How to Change Date Format in Excel - Screenshot of Step 1-3

  4. Choose the date format you want in the box on the right side of the dialog box

    How to Change Date Format in Excel - Screenshot of Step 1-4

  5. Click the OK button

    How to Change Date Format in Excel - Screenshot of Step 1-5

  6. Done!

    How to Change Date Format in Excel - Screenshot of Step 1-6




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

    How to Change Date Format in Excel - Screenshot of Step 2-1

  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

    How to Change Date Format in Excel - Screenshot of Step 2-2-1

    How to Change Date Format in Excel - Screenshot of Step 2-2-2

  3. In the dialog box that shows up, go to the Number tab and click Custom to highlight it

    How to Change Date Format in Excel - Screenshot of Step 2-3

  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

    How to Change Date Format in Excel - Screenshot of Step 2-4

  5. Click the OK button after you have inputted the date format

    How to Change Date Format in Excel - Screenshot of Step 2-5

  6. Done!

    How to Change Date Format in Excel - Screenshot of Step 2-6






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

    How to Change Date Format in Excel - Screenshot of Step 3-1

  2. Type TEXT (can be with large and small letters) and an open bracket sign after =

    How to Change Date Format in Excel - Screenshot of Step 3-2

  3. Input the date or the cell coordinate where the date you want to change the format of is. Then, type a comma sign ( , )

    How to Change Date Format in Excel - Screenshot of Step 3-3

  4. Input the date format you want. Symbolize the day unit with d, the month unit with m, and the year unit with y

    How to Change Date Format in Excel - Screenshot of Step 3-4

  5. Type a close bracket sign

    How to Change Date Format in Excel - Screenshot of Step 3-5

  6. Press Enter
  7. Done!

    How to Change Date Format in Excel - Screenshot of Step 3-7




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”

    How to Change Date Format in Excel - Screenshot of the Data Format Text Box Location

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

How to Change Date Format in Excel - Screenshot of the Home Tab, Data Format Dropdown Button, and Its Short Date & Long Date Choices Location

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

How to Change Date Format in Excel - Screenshot of the Result Example When We Implement the Short/Long Date Default Format to Our Date



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.

How to Change Date Format in Excel - Screenshot of the Language Dropdown Location in the Format Cells Dialog Box, Date Data Format

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.

[$-409]


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.

How to Change Date Format in Excel - Screenshot of the Example of Writing a Date Format in the Custom Data Format Text Box With a Locale Code

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.

How to Change Date Format in Excel - Screenshot of the Date and Time Format Choice Example in the Format Cells Dialog Box

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.

How to Change Date Format in Excel - Screenshot of the Example of Date and Time Format Input 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 Change Date Format in Excel - Screenshot of the Result Example When We Implement the Date & Time Format to Our Data



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.

How to Change Date Format in Excel - Screenshot of the Home Tab, Data Format Dropdown Button, and Its General Choice Location

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

How to Change Date Format in Excel - Screenshot of the Example of a Date with the General Data Type

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.

How to Change Date Format in Excel - Screenshot of the DATEVALUE and TEXT Combination Formula Writing Example

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

How to Change Date Format in Excel - Screenshot of the DATEVALUE and TEXT Combination Implementation Example



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:



Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!





Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!




Follow our tutorial content also on


Hand-Picked CE Tutorials

Excel Calculation

How to Sum in Excel

Subtraction in Excel

Multiplication in Excel

Division Excel Calculation

Average Excel Calculation



Excel Formula

VLOOKUP Excel Formula

Excel IF Function

SUM Formula in Excel

COUNTIF Formula in Excel

COUNT Function in Excel



Excel Tips and Trick

How to Print in Excel

Convert Number to Text Excel

Excel Worksheet Definition

Excel Range Definition

How to Add Columns in Excel



Excel Consultation

Contact Us

Privacy Policy

Affiliate Disclosure

Terms & Condition



© 2021 Compute Expert