Buy Now

The Ultimate Guide: How To Change Date Format In Excel

data formatting date format excel excel tips excel tricks exceltools May 02, 2023

Do you want to learn how to change the date format in Excel?

Whether you're dealing with an incoming data set or working on connecting applications, manipulating date formats can be a tricky process. Fortunately, in this blog post, we'll walk you through exactly how to change the date formatting of your cells so that they appear consistently.

Read on as we cover the following:

  • Date Format in Excel

  • How to Adjust the Date Format

  • Create a Custom Date Format

  • Final Thoughts on How to Change Date Format in Excel

  • Frequently Asked Questions on How to Change Date Format in Excel

Date Format in Excel

Excel allows you to change date formats according to your preferences or how you want to present them to your audience. You can change the default date format to something other than the default format in Excel.

For example, you can display dates as "June 4, 2001" or "06/4/01". Additionally, you can create a custom format in the Excel desktop version.

How to Adjust the Date Format

You can use the "Format Cells" dialog to display dates in different formats in Microsoft Excel to choose from various predefined formats. This method lets you effortlessly adjust the date format of a cell or range of cells.

  1. Select the cells you want to change the date formats.Change The Date Format in Excel

  1. Navigate to the Home tab and select the Number group.Change The Date Format in Excel

  1. Select the Custom drop-down box and choose either the "Short Date Format" or the "Long Date Format" option.Change The Date Format in Excel

The Long Date format, written as Tuesday, January 2, 2001, differs from the Short Date format, which appears as 1/2/2001.

Change The Date Format in Excel

More Number Formats

At the bottom of the drop-down menu is an option called "More Number Formats." By clicking on it, you can access additional date formats.

More Number Formats

To use the More Number Format options, follow the steps below:

  1. Select all the dates you want to format, then click the More Number Formats option.More Number Formats

  1. At the Format Cells dialog box, select the Date Category, and choose a format from the Type Box.

    More Number Formats

  1. You can choose a different locale to have a correct date format. After finalizing, click the OK button.More Number Formats

At this point, you have successfully learned how to use the More Number Formats option.

More Number Formats

Make sure to check out our guide on how to use conditional formatting for more details!

Create a Custom Date Format

There are default formats available, but if you want a custom format, you can create one using a format code.

  1. Choose the range of cells to format.Custom Date Format

  1. Press the Ctrl + 1 keys for the Format Cells dialog box.Custom Date Format

  1. Select Custom from the Category list. Choose a format code from the Type Text Box and click the OK button.Custom Date Format

You now have your customized date format in Excel.

Custom Date Format

To create a custom date, refer to the table below.

Custom Date Format

Final Thoughts on How to Change Date Format in Excel

Changing the date format in Excel can be tricky, but it's worth it for easily understandable data reporting. Double-check your formatting before exporting or sharing the spreadsheet with others to ensure you have no issues.

For more easy-to-follow guides, visit Simple Sheets and the Related Articles section of this blog post. Subscribe to Simple Sheets on Youtube for the most straightforward Excel video tutorials!

Frequently Asked Questions on How to Change Date Format in Excel

Why can't I change the date format in Excel?

The formatting problem in Excel might be caused by imported dates stored as text values instead of real numbers. Remember that dates in Excel are sequential serial numbers.

How can I change the date format in Excel from Month/Day/Year to Day/Month/Year using VBA?

To set the number format for cell A1 as "DD-MM-YYYY," we should access "Number Format" and use an equal sign with the format code in double quotes. Executing this code will apply your preferred format to the cell.

How can I change a date without leaving the formatting?

Follow the steps to change a date without losing formatting:

  1. Select and copy the date cells.

  2. Paste the text into Notepad.

  3. Return to Excel and select the cells to paste the dates.

  4. In the Home tab, go to Number and choose the Text format from the drop-down menu.

Related Articles:

How to Group Sheets in Excel

A Practical Guide To Add Header And Footer In Excel

Excel Remainder: How to Get it With the Excel Mod Function

Want to Make Excel Work for You? Try out 5 Amazing Excel Templates & 5 Unique Lessons

We hate SPAM. We will never sell your information, for any reason.