When you type a date or time in a cell, it displays as a default date and time format .The default date and time format is depended on the regional date and time settings that are specified in Windows Control Panel. The default date and time format can be changed when the regional date and time settings change. You can format numbers in some date and time formats that most of which are not affected by the Control Panel settings. To format the numbers, do the following:
- On the worksheet, select the cells that you want to format.
- On the Home tab, in the Number group, do one of the following:
▫ To display the numbers as short date or long date, click drop-down arrow of the Format Number box, and then click Short Date or Long Date in the list.
▫ To format the numbers as dates or times that you can select date or time format in other languages, click drop-down arrow of the Number Format box, and then click More Number Formats. Or you can click Dialog Box Launcher.
In the Format Cells dialog box, on the Number tab, in the Category box, click Date or Time. In the Type list, select the date or time format that you want to use. In the Locale(location) combo box, select the language setting that you want the dates and times to display in; and then click Ok button to apply these formats.
- When you select the number in the active cell, it displays in the Sample box so that you can preview the number with the format options that you select.
- Sometimes you may see a cell display ####. It means that the cell contains data that has number format which is wider than the column width. To see all text, you need increase the width of column. How to increase the column width.
- To quickly format date or time as default date or time format, press CTRL+SHIFT+# or CTRL+SHIFT+@.
- If the date or time format in the Type list don't meet your need, click Custom in the Category box of the Format Cells dialog box to create your own date or time custom format. When you create the date or time custom format, you need to use the date or time codes. The following table lists the date and time codes.
- For days, months, and years:
|d||Days as 1-31|
|dd||Days as 01-31|
|ddd||Days as Sun-Sat|
|dddd||Days as Sunday-Saturday|
|m||Months as 1-12|
|mm||Months as 01-12|
|mmm||Months as Jan-Dec|
|mmmm||Months as January-December|
|mmmmm||Months as the first letter of the month|
|yy||Years as 00-99|
|yyyy||Years as 1900-9999|
- For hours, minutes, and seconds
|H||Hours as 0-23|
|hh||Hours as 00-23|
|m||Minutes as 0-59|
|mm||Minutes as 00-59|
|s||Seconds as 0-59|
|ss||Seconds as 00-59|
|h AM/PM||Hours as 13 PM|
|h:mm AM/PM||Times as 13:30 PM|
|h:mm:ss A/P||Times as 13:30:09 P|
|[h]:mm||Elapsed time in hours; for instance, 20:09|
|[mm]:ss||Elapsed time in minutes, for instance, 62:25|
|[ss]||Elapsed time in seconds.|
|h:mm:ss.00||Fractions of a second|
For example, you want the date you type in a cell displaying as January 2013. To do this, you need to set the custom format as mmmm yyyy.
This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages: