How to Change Excel Date Format Via the Format Cells Feature
With the use of Excel’s many menus, you can change up the date format within a few clicks.
Make Your Own With Excel Custom Date Format
If you don’t find the format you want to use, select Custom under the Category field to format the date how you’d like. Below are some of the abbreviations you’ll need to build a customized date format.
Using Quick Apply for Long or Short Date
If you need a quick format change from to either a Short Date (mm/dd/yyyy) or Long Date (dddd, mmmm dd, yyyy or Monday, January 1, 2019), there’s a quick way to change this in the Excel Ribbon.
Using the TEXT Formula to Format Dates
This formula is an excellent choice if you need to keep your original date cells intact. Using TEXT, you can dictate the format in other cells in any foreseeable format. To get started with the TEXT formula, go to a different cell, then enter the following to change the format:
is the cell label, and format abbreviations are the ones listed above under the Custom section. For example, =TEXT(A2, “mm/dd/yyyy”) displays as 01/01/1900.
Using Find & Replace to Format Dates
This method is best used if you need to change the format from dashes (-), slashes (/), or periods (.) to separate the month, day, and year. This is especially handy if you need to change a large number of dates.
Using Text to Columns to Convert to Date Format
If you have your dates formatted as a string of numbers and the cell format is set to text, Text to Columns can help you convert that string of numbers into a more recognizable date format.
Replace All: Which will replace all the first field entry and replace it with your choice from the Replace with field.Replace: Replaces the first instance only.Find All: Only finds all of the original entry in the Find what field.Find Next: Only finds the next instance from your entry in the Find what field.
Using Error Checking to Change Date Format
If you’ve imported dates from another file source or have entered two-digit years into cells formatted as Text, you’ll notice the small green triangle in the top-left corner of the cell. This is Excel’s Error Checking indicating an issue. Because of a setting in Error Checking, Excel will identify a possible issue with two-digit year formats. To use Error Checking to change your date format, do the following:
Using Quick Analysis to Access Format Cells
Quick Analysis can be used for more than formatting the color and style of your cells. You can also use it to access the Format Cells dialog.
