Format Numbers As Dates Or Times - Microsoft Support

Display numbers as dates or times

You can format dates and times as you type. For example, if you type 2/2 in a cell, Excel automatically interprets this as a date and displays 2-Feb in the cell. If this isn't what you want—for example, if you would rather show February 2, 2009 or 2/2/09 in the cell—you can choose a different date format in the Format Cells dialog box, as explained in the following procedure. Similarly, if you type 9:30 a or 9:30 p in a cell, Excel will interpret this as a time and display 9:30 AM or 9:30 PM. Again, you can customize the way the time appears in the Format Cells dialog box.

1. On the Home tab, in the Number group, click the Dialog Box Launcher next to Number.

   

   You can also press CTRL+1 to open the Format Cells dialog box.

2. In the Category list, click Date or Time.

   

3. In the Type list, click the date or time format that you want to use.

   Note: Date and time formats that begin with an asterisk (*) respond to changes in regional date and time settings that are specified in Control Panel. Formats without an asterisk are not affected by Control Panel settings.

4. To display dates and times in the format of other languages, click the language setting that you want in the Locale (location) box.

   

   The number in the active cell of the selection on the worksheet appears in the Sample box so that you can preview the number formatting options that you selected.

   

Top of Page