Microsoft Excel 2007 tutorial: format numbers as dates or times

Format numbers as dates or times

IV. Format numbers as dates or times

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.

  Excel 2007 Short Date or Long Date

  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.

  Excel 2007 More Number Formats  Excel 2007 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:

Code Display
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

Code Display
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:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.