One of the nice features of Microsoft Excel is that there are usually multiple ways to perform many popular functions. This is especially true for date formats. Whether you’ve imported data from another spreadsheet or database, or you’re just entering due dates for your monthly bills, Excel can easily format most date styles. Read on to learn how to change date format in Excel.
The instructions in this article apply to Excel 2019, 2016, and 2013.
Thanks to Excel’s many menus, you can change the date format in just a few clicks.
- Choose Warm welcome Tab.
- In the Cells group, select the format drop-down menu and then select format cells.
- On the Number tab in the dialog box, select Format Cells date.
- As you can see, there are several formatting options in the Type field.
You can also check the Locale drop-down menu to select the format that best suits the country you are writing for.
- Once you’ve decided on a format, select it OK to change the date format of the selected cell in your Excel spreadsheet.
Create your own with Excel custom date format
If you can’t find the format you want, select it personalization under the Category field to format the date as desired. Below are some of the abbreviations you will need to create a custom date format.
| Abbreviations used for dates in Excel | |
|---|---|
| Month displayed as 1-12 | m |
| Month displayed as 01-12 | mm |
| Month displayed as Jan-Dec | mmm |
| Full month name January-December | mmmm |
| The month indicated by the first letter of the month | mmmm |
| days (1-31) | i.e |
| Days (01-31) | dd |
| Days (Sun-Sat) | dd |
| Days (Sunday-Saturday) | dddd |
| Years (00-99) | jj |
| Years (1900-9999) | yyyy |
- Choose Warm welcome Tab.
- In the Cells group, select the format and select format cells.
- On the Number tab in the dialog box, select Format Cells personalization. Just like the Date category, there are multiple formatting options.
- Once you’ve decided on a format, select it OK to change the date format for the selected cell in your Excel spreadsheet.
If you prefer to just use your mouse and avoid maneuvering through multiple menus, you can change the date format using the right-click context menu in Excel.
- Select the cell(s) containing the dates whose format you want to change.
- Right-click the selection and select format cells. Otherwise press Ctrl+1 to open the Format Cells dialog box.
Otherwise select Warm welcome > number choose Arrow then select number format at the bottom right of the group. Or in number You can select the drop down list and then select More number formats.
- Choose date or choose if you need a more custom format personalization.
- In the Type field, select the option that best suits your formatting needs. It may take a little trial and error to get the formatting right.
- Choose OK once you have chosen your date format.
Regardless of whether you use the Date or Personalization category, if any of the types begin with an asterisk (*), this format changes depending on the chosen place (site).
Use of the quick application process for long-term or short-term applications
If you need a quick format change for a short date (mm/dd/yyyy) or a long date (dd, mmmm dd, yyyy or Monday, January 1, 2019), there is a quick way to change it in the Excel ribbon .
- Select the cell(s) whose date format you want to change.
- Choose Warm welcome.
- In the Number group, select the dropdown menu and then select one of the two meeting or long date.
Using the TEXT formula for date formatting
This formula is a good choice when you need to keep your original date cells intact. TEXT lets you dictate the format into other cells in any predictable format.
To start with the TEXT formula, go to another cell and then type the following to change the format:
=TEXT(##, “format abbreviations”)
## is the cell label, and the format abbreviations are those listed below the above personalization Section. Example: =TEXT(A2, “MM/DD/YYYY”) is displayed as 01/01/1900.
Use find and replace to format dates
This method works best when you need to change the format of hyphens (-), slashes (/), or periods (.) to separate the month, day, and year. This method is especially useful when you need to change a large number of dates.
- Select the cell(s) for which you want to change the date format.
- Choose Warm welcome > find and select > Substitute.
- In the Find what box, type your original date separator (hyphen, slash, or period).
- In the Replace with box, type what you want to replace with the format separator (hyphen, slash, or period).
- Then choose one of the following options:
- Replace everything: This replaces all entries in the first field and replaces them with your selection in the field Replace with Master.
- Substitute: Replaces only the first instance.
- find all: Finds only the entire original entry in the find something Master.
- find more: Finds only the closest instance from your entry in the find something Master.
Using column text to convert to date format
If your dates are formatted as a string of numbers and the cell format is set to text, the Text to Columns feature can help you convert that string of numbers into a more recognizable date format.
- Select the cell(s) whose date format you want to change.
- Make sure they are formatted as text. (Keep pressing Ctrl+1 to check their format).
- Choose Data Tab.
- Select “Data Tools” in the group text in columns.
- Choose either delimited or fixed width then select Next.
In most cases, Separate should be selected, since the length of the data can vary.
- Uncheck and select all separators Next.
- As part of the column data format Select area date Choose the format of your date string using the drop-down menu, then select End.
Using error checking to change the date format
If you imported dates from another file source or entered two-digit years in cells formatted as text, you’ll see the small green triangle in the upper-left corner of the cell.
This is Excel’s error check that indicates a problem. Due to a setting in error checking, Excel detects a possible problem with two-digit year formats. To use error checking to change your date format, do the following:
- Select one of the cells containing the indicator. You should notice an exclamation point with a drop-down menu next to it.
- Select the drop down menu and choose either Convert XX to 19XX or Convert xx to 20XX depending on what year it was built.
- You should see the date change to a four-digit number immediately.
Using Quick Parse to access formatted cells
The quick analyzer can be used for more than just formatting the color and style of your cells. You can also use it to access the Format Cells dialog box.
- Select multiple cells that contain the data you need to change.
- Choose Fast scan in the lower right corner of your selection, or press Ctrl+Q.
- Select under formatting Text that contains.
- Select from the drop down menu on the right Custom format.
- Choose number then choose either date or personalization.
- Choose OK twice when you’re done.