How to Change the Date Format in Excel?

Excel is one of the finest data handling tools in which dates make up the majority of the spreadsheets. But dates are a little tricky as they often appear in different formats. If you are working with US, UK, or Australian dates, it is important for you to learn how to change date format in Excel. How to change date formats – this blog will walk you through how to do just that, including formulas for certain changes.

Excel reads dates as serial numbers, where every date is translated into some number representing how many days have elapsed since January 1, 2000. This enables some arithmetic on the dates using the SUM or SERIESSC functions to add days or subtract dates. However, these dates look different depending on regional or user settings.

 

Date Formats

1. US Format: MM/DD/YYYY (Example: 12/31/2023)
2. UK Format: DD/MM/YYYY example: 31/12/2023
3. Australian Format: DD/MM/YYYY Example: 31/12/2023

Each format targets its own location, and sometimes you will need to convert your date formats because you are targeting a specific audience or reporting standard.

How to Change Date Format in Excel?

There are different methods used to changed date format in excel:

Method 1. Using Format Cells

The most straightforward method in Excel to change date format is through the “Format Cells” option. Here is how to do it:

1. Choose the Cells: Select the cells containing the dates you want to modify.
2. Right Click: Select Format Cells from the context menu displayed when you right-click over the selected area.
3. Choose Date: From the Format Cells dialog box, select the Number tab and then click on Date in the list on the left side.
4. Select your format: You can choose the desired date format from the list. This will display a preview of how your dates will look.
5. Click OK: After you pick your format, click OK to save your formatting change.

This method is simple and generally applies to changes in date format quite well in Excel.

Method 2: Using Excel Functions

In some cases, you may be forced to change the date format in Excel using formulas. This can happen when you are importing data, which may not recognize the dates. For such a case, here are some formulas that might help you out:

Formula in Excel to Convert Date Format from US to UK

If you have US dates and would like to change them to UK format, you can use this formula. Assume your US date is in cell A1:

=TEXT(A1, “DD/MM/YYYY”)

This will change your date at A1 from US to UK format. The date in the TEXT function assists in defining how you would like to have the date appear.

Excel Change Date Format from US to Australia

The Australian date format is the same as the UK format, that is, DD/MM/YYYY. So, you should be able to directly use the same formula

=TEXT(A1, “DD/MM/YYYY”)

This will convert the US-format dates into an Australian date format also.

Method 3: Applying TEXT Function for Customizing Date Formats

The ability of the TEXT function to change dates is also pretty strong. Here’s how you can use it for custom formats:

1. Select a cell in which you want the formatted date.
2. Enter the formula using the function of TEXT. For instance, if you need to convert the data to “d mmmm, yyyy” format:

=TEXT(A1, “DD/MM/YYYY”)

3. Once you enter the formula, press Enter. That is all there is to it. The cell will now display the date in the format of choice.

The TEXT function allows you to build all forms of user-defined formats except the basic date formats. For instance, you could print out the month in a text format-for example, “January”-by using “MMMM” instead of “MM”.

 

When would you need the Excel TEXT Function?

We use the TEXT function in the following scenarios:

1. We want to display dates in a specific format
2. We wish to display numbers in a specific format or in a printed way
3. We wish to combine the number with text or characters

Additional Tips for Changing Date Formats in Excel

Change Your Regional Setting

Before making changes to date format, you’ll need your regional setting of excel to align with the desired format, and you can achieve this by:

1. Click “File” from the left-hand corner.
2. Click on Options then choose Language.
3. Ensure that your language and locale have been selected.

Date conversions to Text

Sometimes, dates are written as text especially when importing data from another source. Here are the steps you can use in turning text dates into proper date formats:

1. Highlight the cells containing text dates.
2. Click on the “Data” tab on the ribbon.
3. Click on the “Text to Columns.
4. Select “Delimited” and click “Next.
5. Click “Finish” to convert text dates to date values.

Applying Date Picker Add-ins

If you do much date entry, you would do well to apply date picker add-ins that permit one to pick dates through a calendar interface; thus, eliminating format problems at source levels.

Fixing Common Problems

If dates are not showing correctly after changing formats, try these troubleshooting steps

1. Redouble-check that the cells contain dates-not plaintext. If they are text, refer back to the section above and learn how to convert them.
2. Recycle formats again sometimes, just applying the same format again will cure the problem.
3. In addition, look for leading paces. Additional spaces before dates create problem with the formatting. Use the TRIM Function to eliminate the additional spaces.

Conclusion

In Excel, changing date format from US date format accepted into UK, Australian, or other formats is highly beneficial for displaying your information effectively. You can easily convert US dates to UK or Australian formats or, in general, keep date display to suit your needs using more than just a few quite easy ways with the help of Excel. Format Cells is a snap. But to be able to write formulas like the TEXT function gives you flexibility in specific conversions. Knowing how to manage date formats ensures that spreadsheets get across the right information.