To change Excel date format, select the cells containing dates, press Ctrl+1, choose a format under the "Date" category in the Format Cells dialog, or enter a custom format code. This changes how dates appear without altering their underlying values.
Excel stores dates as serial numbers-January 1, 1900, is 1, and each subsequent day increments by 1. The visible format is separate from the stored value. Changing the format alters only the display, not the actual date data used in calculations.
For example, a cell with the serial number 44601 displays as "1/1/2022" in the default short date format. Change it to "Monday, January 1, 2022," and the cell shows the full name of the day and month-but the underlying number remains 44601. This separation allows consistent calculation while supporting multiple visual representations.
When you apply a date format, Excel uses a set of predefined or user-defined codes to interpret the serial number and render it as text. These codes include:
d - Day without leading zero (e.g., 1)dd - Day with leading zero (e.g., 01)ddd - Abbreviated weekday (e.g., Mon)dddd - Full weekday name (e.g., Monday)m - Month without leading zero (e.g., 1)mm - Month with leading zero (e.g., 01)mmm - Abbreviated month name (e.g., Jan)mmmm - Full month name (e.g., January)yy - Two-digit year (e.g., 22)yyyy - Four-digit year (e.g., 2022)Combining these codes creates custom formats. For instance, entering dddd, mmmm dd, yyyy in the Format Cells dialog turns 44601 into "Monday, January 01, 2022."
Format codes are case-sensitive. Using DD produces "01," but dd does the same. However, MMM gives "JAN," while mmm gives "Jan." Always test your format in a sample cell first.
Select the range of cells with dates. Right-click and choose "Format Cells," or press Ctrl+1. In the dialog box, go to the "Number" tab, select "Custom," and enter your desired format code in the "Type" field.
For common formats, pick from the list under "Category." Choose "Short Date" for "1/1/2022," "Long Date" for "Monday, January 1, 2022," or "Custom" to define your own.
After entering a custom code, click OK. The selected cells update instantly. If the format doesn't appear as expected, verify that the code follows the correct syntax and avoid spaces unless intended.
The table below shows frequently used date formats and their corresponding code strings.
| Display Format | Format Code | Use Case |
|---|---|---|
| 1/1/2022 | mm/dd/yyyy | Standard US date entry |
| 01/01/2022 | dd/mm/yyyy | European standard format |
| Jan 1, 2022 | mmm d, yyyy | Reports and dashboards |
| Monday, January 1, 2022 | dddd, mmmm dd, yyyy | Calendar views or formal documents |
| 2022-01-01 | yyyy-mm-dd | Data exports, databases, ISO standard |
Using yyyy-mm-dd ensures sorting works correctly in alphabetical order, which is useful when importing data into systems that expect ISO 8601 format.
A non-obvious point: Excel's regional settings affect default date formats. If your system is set to UK English, "1/1/2022" defaults to January 1, not February 1. You can override this in the Format Cells dialog regardless of system settings.
Many assume changing the format affects the date's value. That's incorrect. A cell formatted as "January 1, 2022" still holds the serial number 44601. This means formulas referencing the cell still work-SUM, DATEDIF, and IF functions treat the value as a valid date.
Another mistake is typing the format code incorrectly. Entering mmmm dd yyyy without a space between mmmm and dd causes Excel to ignore the format. Always use proper spacing between elements.
I once spent 20 minutes troubleshooting a report where dates were displaying as "01/01/2022" despite being formatted as "dd-mmm-yyyy." The issue was a hidden space in the code. After removing it, the format rendered correctly. Always double-check your input.
If dates appear as numbers (e.g., 44601), they're not formatted as dates. Select the cells, go to Format Cells → Number → Date, then choose a format. Excel will convert the serial number to a readable date.
When copying dates between sheets or workbooks, ensure both have matching regional settings. Mismatched settings can cause dates to appear as "#" or "01/01/1900" due to parsing errors.
For bulk formatting, use the Format Painter tool. Select a properly formatted cell, click the Paintbrush icon, then drag over the range. This copies the format, not the value.
When sharing files with users in different regions, consider using the TEXT() function to force a specific format. For example, =TEXT(A1,"dd-mmm-yyyy") returns a text string in the specified format. Note: this converts the result to text, so it cannot be used in date calculations.
Always validate that your date columns are recognized as dates. Use the ISDATE() function (available in newer versions) or check the alignment-dates align to the right in cells, while text aligns left.
Finally, if you're working with imported data, use Text to Columns (Data tab → Text to Columns) to split mixed formats. This helps clean up entries like "01/01/2022 14:30" into separate date and time fields.
Mastering how to change Excel date format improves clarity, consistency, and reliability in spreadsheets. Use custom codes to match reporting standards, and always preserve the original date value for analysis.