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.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!
