Converting the date format in Excel is easier than you might think. You just need to select the cells with the dates, open the Format Cells dialog box, and choose the date format you prefer. This article will guide you through each step in detail, so you can master date formatting in Excel.
How to Convert the Date Format in Excel
In this tutorial, you’ll learn how to change the date format in any Excel spreadsheet. By the end, you’ll be able to convert dates to different formats with ease.
Step 1: Open Your Excel Spreadsheet
First, open your Excel file that contains the dates you want to format.
Make sure you have the necessary data ready. If you don’t have dates in your spreadsheet yet, enter some sample dates before proceeding.
Step 2: Select the Cells with Dates
Click and drag to highlight the cells with the dates you want to format.
You can also hold down the Ctrl key while clicking individual cells if they are not next to each other. This allows you to select multiple non-contiguous cells.
Step 3: Open the Format Cells Dialog Box
Right-click on any of the selected cells and choose ‘Format Cells’ from the context menu.
Alternatively, you can go to the ‘Home’ tab, and in the ‘Number’ group, click the small arrow in the corner to open the Format Cells dialog box.
Step 4: Choose the Date Category
In the Format Cells dialog box, go to the ‘Number’ tab and select ‘Date’ from the list on the left.
You’ll see various date format options on the right. This is where you can choose how your dates will be displayed.
Step 5: Select the Desired Date Format
Pick the date format you prefer from the list and click ‘OK’ to apply.
Formats range from short dates like 1/1/2023 to long dates like January 1, 2023. Choose the one that suits your needs best.
Step 6: Confirm the Format Change
Check your spreadsheet to see the new date format applied to the selected cells.
If it looks good, you’re all set. If not, you can go back and choose a different format.
After completing these steps, your selected dates will be displayed in the new format you chose. This helps in organizing and presenting data more clearly. The same fix applies when a formula such as =A2+7 returns a number instead of a date, which is covered in our guide on adding 7 days to a date in Excel.
Faster Ways to Change the Date Format
- Keyboard shortcut: Select the cells and press Ctrl + 1 (Cmd + 1 on a Mac) to open Format Cells directly.
- Ribbon menu: On the Home tab, open the Number Format drop-down and choose Short Date or Long Date.
- Quick shortcut: Press Ctrl + Shift + # to apply a day-month-year format such as 14-Mar-26.
Change the Date Order for Another Country
In the Format Cells dialog box, select the Date category and open the Locale (location) drop-down. Choose a country, such as English (United Kingdom), and the list of formats changes to that country’s order, such as 14/03/2026 instead of 3/14/2026. This only changes how the dates look. The underlying values stay the same.
Custom Date Format Codes
For full control, choose Custom in Format Cells and type a code in the Type box. These codes control each part of the date, and Microsoft’s date formatting guide has the complete set:
- d or dd: day as 1 or 01
- ddd or dddd: day name as Sat or Saturday
- m or mm: month as 3 or 03
- mmm or mmmm: month name as Mar or March
- yy or yyyy: year as 26 or 2026
Combine them with any separators you like. For example, dd/mm/yyyy shows 14/03/2026, yyyy-mm-dd shows 2026-03-14, and dddd, mmmm d, yyyy shows Saturday, March 14, 2026.
Convert a Date to Text with a Specific Format
If you need the date as text, for example to join it with other words, use the TEXT function: =TEXT(A2,”mmmm d, yyyy”). To build a sentence, try =”Due on “&TEXT(A2,”dd/mm/yyyy”). Keep in mind that the result is text, so it won’t sort or calculate like a date.
Fix Dates That Won’t Change Format
If changing the format does nothing, the dates are probably stored as text. Common signs are dates that line up on the left side of the cell, a green triangle in the corner, or =ISNUMBER(A2) returning FALSE. Here’s how to convert them into real dates:
Use Text to Columns
- Select the column of text dates.
- Go to Data > Text to Columns.
- Choose Delimited, click Next, and click Next again.
- Under Column data format, select Date and choose the order the text uses, such as DMY for 14/03/2026 or MDY for 03/14/2026.
- Click Finish. The values become real dates you can format however you like.
Use DATEVALUE
In a blank column, enter =DATEVALUE(A2) and fill it down, then format the results as dates. DATEVALUE reads the text using your computer’s regional settings, so it works best when the text matches your local date order.
Convert Numbers Like 20260314
Dates exported as eight-digit numbers can be rebuilt with =DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2)). Change the LEFT, MID, and RIGHT positions if the digits are in a different order.
Change Excel’s Default Date Format
The Short Date and Long Date formats come from your Windows settings. In Windows 11, go to Settings > Time & language > Language & region, open Regional format, and click Change formats. Pick a new short and long date style, then restart Excel. Dates formatted with the default styles will follow the new setting.
Troubleshooting
The Cell Shows #####
The column is too narrow for the date. Double-click the right edge of the column header to widen it. If it still shows #####, the cell may contain a negative number, which Excel can’t show as a date.
Dates Turn into Numbers Like 46095
That’s how Excel stores dates: as the number of days since January 1, 1900. The cell has a General or Number format. Apply a date format and the date will appear.
Day and Month Are Swapped
The data came from a source that uses a different date order than your PC. Use Text to Columns and choose the source’s order (DMY or MDY) in step 4. Reformatting alone won’t fix this, because Excel already read the day as the month.
Tips for Converting the Date Format in Excel
- Remember to save your work often to avoid losing changes.
- Explore the International date formats if you are working with global data.
- Apply consistent date formatting across your entire spreadsheet for better readability.
Frequently Asked Questions
How do I create a custom date format?
In the Format Cells dialog box, go to the Custom category and enter your desired format code. For example, use ‘dd-mm-yyyy’ for day-month-year format. Dates that currently show the month first can be switched with the steps for changing mm/dd/yyyy to dd/mm/yyyy in Excel.
Can I apply the same date format to an entire column?
Yes, click the column header to select the entire column, then follow the steps to format the cells.
What if my dates are in different formats?
If they’re real dates, select them all and apply one format in Format Cells. If some are stored as text, convert them first with Text to Columns or DATEVALUE, as shown above, or the format change won’t affect them.
How do I format dates automatically?
Format the whole column before you enter data, or turn the range into an Excel table with Ctrl + T. New rows in a table pick up the column’s date format automatically.
Formula-based options are covered in changing the date format in Excel with a formula.
Matt Jacobs has been working as an IT consultant for small businesses since receiving his Master’s degree in 2003. While he still does some consulting work, his primary focus now is on creating technology support content for SupportYourTech.com.
His work can be found on many websites and focuses on topics such as Microsoft Office, Apple devices, Android devices, Photoshop, and more.