Excel date: Formatting numbers – Dates


Using Excel 2010, you created a table that contains several dates.

In this learning module “ excel date ”, you will discover how to enter and display dates in various ways.

Excel date: HOW DOES IT WORK?

In Excel, dates are stored as integers (whole numbers). The smallest date Excel can process is the number 1, which Excel interprets as January 1, 1900. Subsequent days are assigned the next sequential number.


You can test this by entering the digit 1 into a cell and then formatting the cell as a excel date.

Because of that, a cell that contains the current date, or any other date, can be displayed as a number. The number indicates how many days have passed since January 1, 1900.

You can enter a date using more than one style of notation. As you enter dates, Excel automatically recognizes some but not all possible date notations. If Excel does recognize your input as a date, assuming the General format was in effect at the time, Excel will automatically convert your entry’s format to one of its date formats or to a custom date format.

If a excel date format was already in effect for the cell, that format will continue to be used.

In order for Excel to automatically recognize a date entry, you have to be wary of the notation used. If you enter a date using just dashes or slashes to separate numbers, the first number will be interpreted as the month, the second as the day, and the third as the year.

For example, you can enter the date with…

  • hyphens (2-9-10) or
  • slashes (2/9/10) or
  • an abbreviated or spelled-out month (2 Sept 2010 or Sept 2, 2010 or Sept 2 or 2 Sept or
    September 2, 2010 or 2 September 2010).

Excel automatically recognizes all the above notations as dates and will apply one of its date formats to the relevant cell.

Excel dates, like numbers, are by default aligned to the right.

If you are not satisfied with Excel‘s automatic format selection, you can explicitly set a date format yourself. In fact, it is recommended that to maintain better control over your table’s appearance that you do just that. To explicitly set a date format, select a cell or range of cells. Then, on the Home tab, in the Number group, click the dialog box launcher.

The Format Cells dialog box will appear.

In the Sample area you will see how the contents of the active cell will appear when the current settings are applied. If you selected multiple cells, Excel will show you the contents of the first selected cell.

In the Type area you can specify whichever date format you like. You can choose from a variety of format types.

In this example, the 14-Mar-01 type was chosen.

Conclude by clicking OK. The cells will be formatted accordingly.


Did you like it? Then share it or sign up!

You can share this article with your friends or sign up for our newsletter for further information.

Leave a comment

Fatal error: Call to undefined function stc_get_connect_button() in /usr/www/users/soluzi/en_excelhelfer/wp-content/plugins/simple-twitter-connect/stc-comments.php on line 231