How to change Excel date format and create custom formatting

This Excel tutorial explores date formatting, covering default and custom formats, locale conversion, and troubleshooting. Dates and times are fundamental in Excel, yet their varied display and consistent internal storage can be confusing. Understanding this internal representation saves significant time.

This first part focuses on:

• Excel's date storage mechanism
• Converting dates to numbers
• Default date and time formats
• Changing date formats
• Converting date formats to different locales
• Creating custom date and time formats
• Troubleshooting date formatting issues

Excel's Date and Time Storage

Excel stores dates as sequential integers representing days since January 1, 1900 (1 = January 1, 1900, 2958465 = December 31, 9999). Times are stored as decimals (0.0 = 00:00:00, 0.99999 = 23:59:59), representing a fraction of a day. Combined date and time values are decimal numbers with the integer portion representing the date and the decimal portion representing the time.

Converting Dates to Numbers

To view the serial number representing a date:

1. Format Cells: Select the cell, press Ctrl 1, choose "General," note the number shown, and click "Cancel" (or "OK" to permanently change the cell's value).
2. DATEVALUE and TIMEVALUE functions: Use =DATEVALUE("mm/dd/yyyy") for the date's serial number and =TIMEVALUE("hh:mm AM/PM") for the time's decimal. Combine them with & for both date and time. Note that dates before 1900 are not supported.

Default Date Formats

Excel's short and long date formats are determined by your Windows Regional settings. These are marked with an asterisk (*) in the "Format Cells" dialog.

Changing Default Date and Time Formats

To change the defaults:

1. Go to Control Panel > Region and Language (or Clock, Language, and Region > Region and Language > Change date, time, or number format).
2. On the "Formats" tab, select your region and modify the date and time formats.
3. For more customization, click "Additional settings" and adjust the short and long date formats.

Quickly Applying Default Formatting

To quickly apply default date/time formatting:

1. Select the cells.
2. On the "Home" tab, in the "Number" group, click the arrow next to "Number Format" and choose the desired format (short date, long date, or time). Alternatively, select "More Number Formats" for additional options. Ctrl Shift # applies the dd-mmm-yy format.

Changing Date Formats

To change a cell's date format:

1. Select the cells.
2. Press Ctrl 1 ("Format Cells").
3. Choose "Date" in the "Category" list and select the desired format.

Converting Date Formats to Different Locales

To change the date format based on a different locale:

1. Select the cells.
2. Press Ctrl 1 ("Format Cells").
3. Select the desired locale under "Locale (location)".

Creating Custom Date Formats

To create a custom format:

1. Select cells.
2. Press Ctrl 1 ("Format Cells").
3. Choose "Custom" and enter your format code using codes like m, mm, mmm, mmmm, d, dd, yy, yyyy, h, hh, m, mm, s, ss, AM/PM.

Creating Custom Formats for Different Locales

To display dates in another language, use a locale code within square brackets, preceded by $, e.g., [$-409] (English, US).

Troubleshooting

• ##### error: Cells are too narrow; widen the column.
• Negative numbers as dates: Switch to the 1904 date system or use the TEXT function (returns text, not a number).
• Dates imported as text: Use DATEVALUE or "Text to Columns" to convert.

This concludes the first part. The next part will cover inserting dates and times.

(Note: The image URLs are assumed to be valid and are included as requested. I cannot display the images directly.)

The above is the detailed content of How to change Excel date format and create custom formatting. For more information, please follow other related articles on the PHP Chinese website!