Formatting Date and Time in Excel 2010

Dates can be entered in almost any format in Excel 2010. You can type them in the various formats, e.g. 7/20, 20-July, July 20, 7/20/10, 20-July 2010, but by default they will be displayed as 20-July, 7/20/2010 or 20-Jul-10 once you hit the Enter key. In the case wherein the year is not specified, e.g. 7/20, the current year is assumed by Excel.

It’s important to understand that Excel’s default date displays are controlled by Windows Region and Language settings, not by Excel itself. However, users have the ability to control the format of the specific date in Excel.

You can format dates in two ways: you can enter the dates first and then format them later on, or you can pre-format blank cells before entering the dates.

1. Open Excel 2010.
2. In the cells, type in the dates. You will notice that in whatever format you type in the dates, they automatically change to the default format once you press the Enter key.
3. Select the cells you want to format.
4. On the Home tab, look for Number and click on the (General) dropdown menu. Select More Numbers Format…

5. In the dialog box, Number tab should be selected. Under Category, select Date.
6. Under Type, you can now choose the format that you prefer for the dates. Click OK.

In Excel 2010, Time is entered as hours:minutes:seconds. Time is displayed depending on how you type them inside the cells. For example:

– If you type in 6:30, not indicating whether it’s AM or PM, AM is assumed if PM is not specified.
– If you enter 6:30 P (or lower case, p) or 6:30 pm, it is displayed as 6:30 PM.
– If you type in 0:30, it is displayed as 12:30 AM; if PM is specified – 0:30 PM (or lower case, pm), it is displayed as 12:30 PM.
– 6: is displayed as 6:00 (assumed as AM), while 6: P is not a valid time. For it to be valid, you must have something following the colon, for example: 6:0 P.
– 6:30:45 is displayed as 6:30:45 AM which means 6 hours 30 minutes 45 seconds.

Excel stores dates and times as numbers, e.g. May 1, 2010 is stored as 40,299 which is the number of days since Jan 1, 1900. 6:30 PM is stored as 0.770833333333333.

Since they are stored as numbers in Excel 2010, it means that we can use them as the functions and formulas. Because dates and times are numbers, they are by default right aligned in cells. If they are left aligned, they are treated as text by Excel and cannot be used in functions and formulas.


Excel 2010 training tutorials


"30-Day Money Back

Notify us within 30 days of subscribing and you will receive a full refund with no questions asked

Subscribe for 1 Year and Get VIP Access

Subscribe to our free newsletter for free videos and other tips: