How to Change Date Format In Excel?

If you work with date values in Excel, it would be useful to know the different formatting options you can use with dates.

Any date or time value in Excel would be a number in the back end that would represent that date or time. for example, 1 represents 1 Jan 1900, 2 represents 2 Jan 1900, and so on. Similarly, 45291 would represent 31 Dec 2023.

While every date is pegged to a corresponding serial number in the back end, Excel allows you a lot of flexibility in the way you can show this date in a cell.

In this tutorial, I will show all the options you can use to change date formats in Excel.

Some of the things I cover in this tutorial will be basic, but there will also be many advanced date formatting tricks sprinkled across this tutorial.

Change Date Format Using Option in the Ribbon

One of the easiest ways to change the date format is by using the number formatting option in the ribbon.

It only offers the option to format the cells in the ‘Short Date’ or ‘Long Date’ format. If you want to format the dates differently, you’ll have to use other methods covered in this article.

Below are the steps to quickly change the date format of the cells using the formatting option in the ribbon:

1. Select the cells that have the dates you want to format.
2. Click the Home tab option in the ribbon.

3. In the Number group, click on the format options dropdown.

4. Select from the Short Date or Long Date format option

As soon as you select the option from the ribbon, the format of the cells will be changed, and the dates will now be shown in the selected date format.

Below is an example where I have the same date shown in the short date format in column A and shown in long date formatting column B

The Short and Long date format you see in the drop-down is taken from your system’s Region and Language settings. In this example, the short date format is in the dd-mm-yyyy format. It could be different on your system based on your region settings.

If you go to your Control Panel and change the date formats there, it will automatically change in this dropdown as well (how to do this is covered in the next section)

Note: This technique would only work if the cells have numeric values (as dates and time values are stored as numbers in the back end in Excel). In case the dates are in a format that Excel does not recognize as a proper date format, the above steps would not have any impact on the cells.

Changing Default Date Formats in Excel

As I mentioned, the default Short and Long date format that you see in the formatting drop-down in the ribbon is taken from your Window’s region and time settings (i.e., your operating system time settings).

Below are the steps to change the default Short and Long date format in Excel:

1. Open Control Panel on your system (you can use Windows search to search for it)

2. In the Control Panel window, click on the Change date, time, or number formats option

3. In the Region dialog box that opens up, make sure the Formats tab is selected.

4. Change the Short date or the Long date format by clicking on the drop-down and selecting from the given options.

5. Click on Apply

Once you’re done making these changes, you will notice that the specified date format is now available in the formatting drop-down in Excel.

If you want a date format other than the ones that are already there in the drop-down in the Region dialog box (in step 4), follow the below steps:

1. Click on the Additional Settings button

2. In the Customize Format dialog box that opens up, select the Date tab

3. Enter the Short Date and the Long date format you want

Also read: Calculate Fiscal Year from Date in Excel

Format Cells (Different Date Formats + Creating Custom Date format)

While getting the Short Date and the Long Date format in the ribbon is quick and easy, it’s quite limiting (i.e., you don’t have a lot of flexibility to change the format of the date).

If you want full customization controls for dates in Excel, you can use the Format Cells dialog box.

While there are already a lot of inbuilt date formats in the Format Cells dialog box, it also allows you to create your own customized date format in case you cannot find the one you need.

In-Built Date Formats in the Format Cells Dialog Box

Below are the steps to use the format cells dialog box to change the format of dates in Excel:

1. Select the cells for which you want to change the date format
2. Hold the Control key and then press the 1 key. This will open the Format Cells dialog box. Alternatively, you can also click on the Home tab and then click on the dialog box launcher in the Number group.
3. In the Format Cells dialog box, make sure the Number tab is selected.
4. Click on the Date option in the category list.

5. Select the date format you want in the Type section, where the different date formats are shown. You will also be able to see the preview of the date in the active cell in the Sample area (right above the Type section)

6. Click OK

The other steps would apply the selected date format on the selected cells.

Advance Excel Knowledge: You would notice any star/asterisk symbol before the first two date formats, which indicates that these are the default date formats picked up from the Windows region setting. These are the same formats you also see in the formatting drop-down in the ribbon in Excel.

Creating Custom Date Format using Format Cells Dialog Box

If in case, you still don’t find the date format you’re looking for, you can create your own using the custom date format using the Custom option in the Format Cells dialog box.

Below are the steps to create your custom date format in Excel:

1. Select the cells for which you want to change the date format
2. Hold the Control key and then press the 1 key. This will open the Format Cells dialog box.
3. In the Format Cells dialog box, make sure the Number tab is selected
4. Select the Custom option in the Category list

5. In the Type field, enter the date format that you want to apply to the selected cells.

6. Click OK

While you are typing the date format in the Type field, you will also be able to see the live preview in the Sample section.

Advanced Excel Knowledge: Instead of building the custom date format from scratch, you can also edit an existing Date format. To do this, select the ‘Date’ option in the Category list on the left, select any of the date formats which is closer to what you want, then click on the ‘Custom’ option again. This will show you the custom format for the selected date format that you can edit.

If you’re not very well versed with how to create custom date formats, below I have a table that shows the meaning of common date format codes:

Converting Dates to Other Locales

It’s common for people who are working with dates data in Excel to share the file with someone in a region that uses a different date format.

For example, someone working in the US (where the date format is mm/dd/yyyy) shares the file with some in the UK (that uses the date format dd/mm/yyyy).

While Excel takes care of this conversion in the back end and shows the date in the format based on your system settings, it could be confusing.

Imagine a video call where a person from the US and the UK are looking at the same Excel file, but the date formats are different.

Thankfully, Excel allows you to change the locale of your date format. So if you are in the UK working with the client in the US, you can create your file using the US locale so that the dates look exactly like it would look on your client’s system.

Here is how to change the Dates to Other Locale in Excel:

1. Select the cells in which you have the dates for which you want to change the locale
2. Hold the Control key and then press the 1 key. This will open the Format Cells dialog box.
3. In the Locale (Location) drop-down, select the Locale you want to set for these cells.

Note that the date locale would only be changed for the selected cells and not for the entire worksheet or workbook

Creating Custom Date Format For Other Locales

In the previous section (Converting Dates to Other Locales), I showed you how to change the locale so you can choose from some pre-existing date formats and show them in other locales/languages.

But what if you want to create your own custom date format and show that date in another language/locale?

This can also be done using custom dare formatting and knowing the code for the locale you want to use.

Below is an example of some codes for some of the languages that you can use in Excel:

• [$-409] – English, Untitled States
• [$-809] – English, United Kingdom
• [$-407] – Arabic, Egypt
• [$-1004] – Chinese, Singapore
• [$-445] – Bengali, India
• [$-413] – Dutch, Netherlands
• [$-407] – German, Germany
• [$-2c0a] – Spanish, Argentina

You can find the full list of Windows Locale codes here.

To use these, you need to use the code (such as [$-809]), where you can get the number for the locale from the link I have given and put that in square brackets, preceded by a dollar sign and a dash.

Here is how to use this code in Excel to show dates in German (or any other language for which you have the code):

1. Select the cells in which you have the dates for which you want to change the locale.

2. Hold the Control key and then press the 1 key. This will open the Format Cells dialog box.
3. Select the Custom option

4. Enter the code followed by the date format you want. For example, if I only want to show the full month names and the year value in German, I can use the following code:

[$-407] mmmm, yyyy

Here is how my dates look like after I have applied the custom date format.

Change Date Format When Dates are in Text Format

It’s common for people to receive their Excel files with dates that are in text format.

In my work, I have often seen that the data I download from some databases or many web-based tools have dates that are formatted as text.

One issue with dates formatted as text is that you won’t be able to use the custom formatting options we have seen above to format the date.

This is because the date is identified as a text string by Excel, and it doesn’t allow you to format it like a date.

Thankfully, Excel has a function called DATEVALUE that allows you to convert dates in the text format back into numerical values corresponding to that date.

Once you have the numerical value for each date, you can then format it as a date (as dates are anyway stored as numbers in the back end in Excel)

Let me show you this with an example.

Below I have a dataset where I have some dates in the text format in column A. Note that the dates are aligned on the left, which is also an indication that these are formatted as text.

Here is the formula that will give me the numerical value for each date:

=DATEVALUE(A2)

Enter this formula in cell B2 and copy this for the entire column to get the numerical values for all the dates in column A.

Once you have the numerical values, you can then format it as a regular date.

Note: DATEVALUE would only work with dates that are in a format that Excel recognizes as date. In case you have a date in a format that is not a recognized date format in Excel (such as 01.Jan.2024 or 01st Jan 2024), you won’t be able to use the DATEVALUE function. You can check out my other tutorial that talks about all the scenarios to convert text to date in Excel.

Formula to Change the Date Format in Excel (TEXT)

TEXT function in Excel allows you to take a date and then format it using the format code in the formula itself.

For example, you can use it to use data as the input and only show the day name or the month name, or any other custom date format you want.

The difference between using the TEXT formula and other methods that we have covered to format the date is that with the TEXT formula, the result you get is a text string.

Let me show you how it works with an example.

Below I have some dates in column A, and I only want to show the full-day name, along with the day value and month value.

Here is the formula that will do this:

=TEXT(A2,"dddd, dd-mmm")

Enter this formula in cell B2 and copy it for all the other cells.

The above TEXT formula takes two arguments:

• value – this is the date value (or the cell reference that contains the date value) that we need to format. Note that this needs to be a proper date format supported by Excel.
• format_text – This is the format code that would be used to format and show the date. This needs to be in double quotes. You can create your own custom formats (just like you can in the Format Cells dialog box).

One important thing that you need to remember about this method is that the result you get is going to be a text string and not a date value or a numerical value.

This is useful when you want the date in the text format (or when you want to add a suffix or prefix to your date.

For example, I can use the below formula to get the date along with the text deadline appended in the beginning:

 ="Deadline: "&TEXT(A2,"dddd, dd-mmm")

Common Issues When Changing Dates Formats in Excel (& their Fix)

Let’s look at some of the common issues you may face while you are trying to change the format of dates in Excel

Dates Shown as Hashtags

If you see the dates being shown as hashtags, there are two possible reasons for this.

Column Width Is Not Wide Enough

When you change the date format, and it suddenly starts showing as hashtags instead of the in that changed format, the most likely reason is that the column width is less and needs to be increased.

Below I have an example where I have some dates that I have formatted as long dates (where it shows the full day name along with the full month name, day value, and the year).

Since the column width is not big enough to accommodate all the dates, the ones that can be accommodated are shown as dates, and the rest are shown as hashtags.

The easy fix is to increase the column width

Negative Dates are Shown as Hashtags

Dates are supposed to be positive numbers in the back end of Excel.

So if, for any reason, you end up getting numbers that are negative and you try and format these as dates, these are going to show up as hashtags.

In most cases, this could be because you are getting the value as a formula result which is giving you a negative number, or it could be a data entry issue.

To fix this, you need to make sure that the negative numbers are converted into positive numbers, and then the date formats are applied to it.

You can do this using the ABS function, which gives you the absolute value and removes the negative sign.

Also read: How to Stop Excel from Changing Numbers to Dates Automatically

Dates Not in the Right Format

Another common issue you can face when changing the date format is that the date you have is in the incorrect format.

There is a set of pre-existing formats that Excel considers as proper dates.

So if you have a date that is in the right format, Excel knows the numeric number that should be used corresponding to this date.

And this is how. it allows you to change the format of that date while keeping that same numeric number in the back end.

But if you have a format that is not recognized by Excel as a proper date format, you will not be able to associate it with the right numeric value in the backend.

For example, 01-Dec-2024 is a proper date format that Excel understands. However, 01.Dec.2014 is not, and Excel treats it as a text string.

So if you have the dates in the wrong format and are being treated as a text string, you first need to ensure that these are converted into proper date format before you try to change its format.

Dates Formatted as Text

Another common issue is that you may get your dates in the right format, however, these are formatted as text.

This can easily be fixed using the date value function, as I’ve covered in the ‘Change Date Format When Dates are in Text Format’ section above in this article.

In this article, I showed you how to change the date formats in Excel using different methods, such as the options in the ribbon or the Format Cells dialog box, or the TEXT function.

I also covered how you can change the locale on your system so that you can get dates in different languages as well.

Other Excel articles you may also like:

• Combine Date and Time in Excel
• Convert Date to Text in Excel
• How to Convert Serial Numbers to Dates in Excel
• How to Compare Dates in Excel (Greater/Less Than, Mismatches)
• Convert Text to Numbers in Excel
• How to Convert Numbers to Text in Excel
• Get Day Name from Date in Excel
• Calculate Number of Weeks Between Two Dates in Excel