The Ultimate Guide: How To Change Date Format In Excel
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.
-
Select the cells you want to change the date formats.
-
Navigate to the Home tab and select the Number group.
-
Select the Custom drop-down box and choose either the "Short Date Format" or the "Long Date Format" option.
The Long Date format, written as Tuesday, January 2, 2001, differs from the Short Date format, which appears as 1/2/2001.
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.
To use the More Number Format options, follow the steps below:
-
Select all the dates you want to format, then click the More Number Formats option.
-
At the Format Cells dialog box, select the Date Category, and choose a format from the Type Box.
-
You can choose a different locale to have a correct date format. After finalizing, click the OK button.
At this point, you have successfully learned how to use the More Number Formats option.
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.
-
Choose the range of cells to format.
-
Press the Ctrl + 1 keys for the Format Cells dialog box.
-
Select Custom from the Category list. Choose a format code from the Type Text Box and click the OK button.
You now have your customized date format in Excel.
To create a custom date, refer to the table below.
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:
-
Select and copy the date cells.
-
Paste the text into Notepad.
-
Return to Excel and select the cells to paste the dates.
-
In the Home tab, go to Number and choose the Text format from the drop-down menu.
Related Articles:
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.
