Change Date Format in Excel: mm/dd/yyyy and Custom Codes

Last updated July 2026. To change the date format in Excel, select the cells, press Ctrl+1 to open Format Cells, choose the Date category, and pick a format, or choose Custom and type a code like mm/dd/yyyy for a two-digit month and day and a four-digit year. If the format will not change, the cells are not real dates yet: they are text, and you have to convert them to numbers first. Format Cells changes how a real date looks; it cannot reformat text.

Dates are the field a PDF conversion mangles most. One column comes in as 03/04/2026, another as 4-Mar-26, a third as plain text that Excel will not sort. Getting every date into one consistent format is usually the first thing you do to a converted statement or report, because nothing else, sorting, filtering, or a monthly SUMIFS, works until the dates are real and uniform.

How do I change the date format in Excel?

Select the cells, press Ctrl+1 (Cmd+1 on a Mac) to open Format Cells, click the Date category, and choose the display you want from the Type list. The underlying value does not change, only how it shows on screen, so the same serial date can read as 3/4/2026, March 4, 2026, or 2026-03-04 depending on what you pick. Click OK and every selected date switches to the new look at once.

Format Cells is the safe way to do this because it never alters the real value. March 4, 2026 is stored as the number 46085 no matter which format you apply, so a formula that references the cell keeps working and the date still sorts chronologically. You are only changing the mask, not the data underneath.

How do I change the date format to mm/dd/yyyy?

Open Format Cells with Ctrl+1, click the Custom category at the bottom of the list, and type mm/dd/yyyy in the Type box, then click OK. That forces a two-digit month, a two-digit day, and a four-digit year, so 3/4/2026 becomes 03/04/2026 and lines up neatly in a column. Use m/d/yyyy instead if you want single digits with no leading zero.

Custom codes are worth knowing because the built-in list rarely has the exact layout you need. The building blocks are simple: d is the day, m is the month, y is the year, and repeating a letter pads or expands it.

How do I create a custom date format in Excel?

In Format Cells, pick the Custom category and build the code from d, m, and y in the Type box. One letter gives the shortest form, so m is 3 and d is 4; two letters pad to two digits, mm is 03; three letters spell the abbreviation, mmm is Mar and ddd is Wed; four letters give the full name, mmmm is March and dddd is Wednesday. Mix them with slashes, dashes, spaces, or commas to get any layout you want.

A live preview at the top of the dialog shows the selected cell in your code as you type, so you can adjust before committing. Once you build a custom format, Excel remembers it in that workbook under the Custom category, which makes it easy to reapply to other columns.

Why won't Excel let me change the date format?

If you apply a date format and nothing happens, the cells hold text, not dates, and Format Cells only restyles real dates. The giveaway is alignment: real dates sit on the right of the cell by default, while text dates sit on the left. Text dates also refuse to sort chronologically and return zero from date math, because to Excel they are just a string of characters that happens to look like a date.

Convert them first. Select the column, open Data, click Text to Columns, click Next twice, choose Date on the third screen with the order that matches your data (MDY for U.S. dates), and finish. Excel reparses each string into a real date, and then Format Cells works normally. This is the exact problem a conversion leaves behind, and there is a full walkthrough for when your dates come in stored as text.

How do I convert a date to text in a specific format?

Use the TEXT function when you need the date as an actual text string in a chosen format, for a label, a file name, or a merged sentence: =TEXT(A2,"mm/dd/yyyy") returns the text "03/04/2026". The second argument uses the same codes as a custom format, so =TEXT(A2,"mmmm yyyy") gives "March 2026" for a report heading. The result is text, so it no longer does date math, which is exactly why you use it only for display and joining.

This matters when you combine a date with other text, because a bare =A2&" invoice" returns the serial number 46085 rather than the date. Wrapping the date in TEXT keeps it readable: =TEXT(A2,"m/d/yyyy")&" invoice" gives "3/4/2026 invoice".

How do I change the default date format in Excel?

The short date default (what Excel uses when it recognizes a typed date) comes from your operating system region setting, not from Excel itself. On Windows, change it in Settings, Time and language, Language and region, Regional format; on a Mac, in System Settings, General, Language and Region. Excel reads that setting for its default short and long date, so switching your region from English (United States) to another English locale flips the default from month-first to day-first.

Because the default is regional, a file that opens as 3/4/2026 on a U.S. machine can open as 4/3/2026 on another, which is a real risk when a date could be either a month or a day. The safe habit for anything shared is to apply an unambiguous custom format like yyyy-mm-dd or d-mmm-yyyy so the date reads the same everywhere.

How do I show the month name or day of the week?

Use the letter forms in a custom code: mmm shows the short month (Mar), mmmm shows the full month (March), ddd shows the short weekday (Wed), and dddd shows the full weekday (Wednesday). So d-mmm-yyyy gives 4-Mar-2026 and dddd, mmmm d, yyyy gives Wednesday, March 4, 2026. The cell still holds the real date, so it keeps sorting and calculating correctly while displaying words instead of numbers.

To pull just the month or weekday into its own cell as text, use TEXT: =TEXT(A2,"mmmm") returns "March" and =TEXT(A2,"dddd") returns "Wednesday". That is handy for grouping converted transaction rows by month before you run a pivot table or a monthly total.

Getting clean dates from the start

Every one of these fixes is easier when the dates arrive as real dates instead of text. A clean PDF to Excel conversion keeps the date column as genuine dates you can format in one click, rather than a wall of left-aligned strings you have to reparse. When the end goal is your books rather than a spreadsheet, you can skip the reformatting entirely and send each dated transaction straight into QuickBooks, where the date field is already understood.

Dates are one step in a longer cleanup. For the full order of operations on a freshly converted file, from text dates to duplicates to numbers stored as text, follow the guide to cleaning up data after a PDF conversion.