DATES AND TIMES IN EXCEL
In this lesson we will look at how Excel sees Dates and Times and how formatting a cell affects the data within it. We will also look at the Undo/Redo feature as well as Custom formats and the Format Painter. This will give us a good background before we move on to what Excel is best at - Working with Numbers - in the next lesson.
You may or may not think that it is odd that we do not go into great detail with formatting cells. This is because while it may enable us to make a spreadsheet look very pretty, it is often at the sacrifice of correctly setting up your spreadsheet, or in other words, the nuts and bolts. We feel that whilst Formatting is pleasing to the eye, it should not be covered too deeply at this stage at the expense of the real workings of a spreadsheet. However, having said this, we do encourage you (as suggested below) to play about with any formatting not discussed here and direct any questions you may have to us.
The term Format according to the Microsoft Dictionary and Thesaurus is:
This pretty much sums it up! The big difference with formatting in Excel as opposed to many other Office Applications is that there is a LOT to choose from! This is because Excel needs to be able to work with numbers of all types and from all corners of the globe, as well as text.
On top of this we can also apply formats of our choice to the cells themselves as well as any data contained within them. As far as numbers alone go Excel has over 100 different types of formatting that we can apply. If this is not enough we can even define our own with Custom Formats (discussed below).
It is our belief that there are two very important facts that we should all be aware of when working with formats in Excel, these are:
If you remember nothing else about formatting then remember these two facts. We will explain the second point in detail later in this lesson. Let's for now look at the first.
Whenever we enter a number in a cell on a new worksheet Excel will take an educated guess at what type of format it should apply. By default all cells in Excel have what is known as a General format. Most of the time you will find yourself accepting the formatting that Excel applies, but at other times you may wish to change it. The context in which we are discussing formatting here is not the font type, size or color, but rather the Number format eg; Dollar, Percentage, Decimal places etc.
We must stress however, that it is far safer to always leave numbers right aligned and text left aligned (Excels default). This way you can tell at a glance what is a number and what is not!I expect by now you are thinking, "I already know the difference between a number and text - it's obvious!"
This is not always the case in Excel. It is quite possible for numbers to appear as text and text to appear as numbers. This may not be quite the case when Starting to use Excel, but is very likely as you delve deeper and create your own spreadsheet.
Dates in Excel
You will find that Dates and Times in Excel can often seem confusing! But they often play a critical part in most spreadsheets so it is important that you have an understanding of how Excel interprets them.
Dates are seen by Excel as whole numbers, Starting from the number 1 for 1-January-1900, the number 2 for 2-January-1900 and so on. . . . . . . . . . These are known as serial numbers. This is so we can use them in calculations such as adding and subtracting etc. We will show you examples of date formulas in the next lesson.
How we enter dates in Excel is extremely important. If we do not insert a date in a valid form, Excel will not know that it is a date. You should be able to tell immediately if a date you enter is valid or not as Excel will align a valid date to the right of your cell (Number) and align a possible invalid date to the left (Text). When Excel recognizes a date as a valid date it will change from the General format (default for all cells) to a built-in date format.
The type of date format Excel defaults to is not set from within Excel, but from the Regional Settings within the Widows Control Panel. (See HELP to find this, as the specific location may be different depending on which version of Excel you are using). As these settings are regional you will most likely simply accept the format applied.
When we enter dates on a Worksheet we must also use a valid date separator, such as 12/12/2004 or 12-12-2004. If we want our date to look different to this we must format it after we have entered it. Lets try a couple of simple exercises to stress what we have discussed so far.
Your date should now appear as 12/12. I say appear because while we may have changed its appearance we have NOT altered its underlying value which is 12/12/2004 or 38333. If you are still selected in cell A1look in the formula bar and you should see 12/12/2004. So while it may appear we have dropped the year from the date we have not!In fact it is not possible to enter a valid date that has no Year, Day or Month. We could in fact format this cell to appear as 1/1/1985 and still have the true value of 12/12/2004 or 38333. We will explain this when we go on to Custom Formats.
If we enter a date into Excel and omit the day eg; Dec-2004 Excel will recognize this as a valid date, but you should be aware that Excel will (by default) assign the first day of the month to the date. This means that while you may only see Dec-2004 in the cell, the underlying value of the date will be 12/1/2004 or 37998. This means that it is not possible to have a valid date in Excel that does not have a day of the month assigned to it. Times in Excel
Ok, if you are still with me let's now look at Times in Excel. While dates in Excel are seen as whole numbers (serial numbers) Starting from 1, Times are seen as a portion of a day (decimal fractions) with 1 being equal to 24:00:00 or a whole day. This means that:
We can see this by entering any one of the above times in Excel and then formatting the cell containing the time as General, just as we did with the date. There are only three ways you can enter a valid time in Excel.
Make sure your cells are formatted as General
If you just typed 5:30 Excel will (by default as your cells are formatted as General by default) base your time on the 24-hour clock and store your time as 5:30:00 AM. In other words by default it sees all unspecified times as 24-hours but stores them based on a 12-hour clock.
The easiest way to see this is to type 5:30 in any cell, then select that cell and look in the Formula bar. No matter which method we use to enter times we must separate the hours, minutes and seconds by a : (colon). If we omit the minutes and/or seconds Excel will (by default) assign zero minutes and/or zero seconds. So entering a time, as 5 P will force Excel to see it as 5:00:00 PM. Obviously this is not the case for a time entered based on a 24-hour. Entering 17 will be seen as nothing more than the number 17.
Entering a Date and Time in Excel
Now that we have covered the fundamentals of dates and times, we can have a quick look at entering dates and times into the same cell. To enter a valid date and time in the same cell, you simply type any valid date, then a space and then any valid time. Excel will then store this as a whole number for the date (serial number) and a portion of a day for the time. (decimal fractions).
On the Home tab in the number group click the dialogue box launcher next to number. You can also press ctrl+1 to open format cells dialogue box.
In the Category list select Date or Time
Click on the date or time format required.
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
Go back to:
|Lesson 1 - Excel Fundamentals|
|Lesson 2 - Starting Excel and Excel Workbooks|
|Lesson 3 - Excel Toolbars and Task Panes|
|Lesson 4 - Excel Worksheets|
|Lesson 5 - Excel Cells and Navigating a Worksheet|
|Lesson 6 - Excel Cut/Copying and Pasting Data|
|Lesson 7 - Excel Copying with the Fill Handle|
|Lesson 8 - Excel Paste Special|
|Lesson 9 - Excel Insert Command|
|Lesson 10 - Excel's default options|
|Lesson 11 - Excel's Undo and Redo|
|Lesson 12 - Excel's Format Painter|
|Lesson 14 - Excel's Custom Formats|
|Lesson 15 - Excel Formulas|
|Lesson 16 - Excel Cell References|
|Lesson 17 - Excel: Avoid Typing|
|Lesson 18 - Excel Formulae Arguments & Syntax|
|Lesson 19 - Excel Autosum Formula|
|Lesson 20 - Excel Auto Calculate|
|Lesson 21 - Excel's Insert Function|
|Lesson 22 - Excel's Useful Functions|
|Lesson 23 - Excel's Named Ranges|
|Lesson 24 - Excel's Constants and the Paste Name Dialog|
|Lesson 25 - Excel's Calculations|
|Lesson 26 - Excel Comments Cell|
|Lesson 27 - Excel Find and Replace|
|Lesson - 28 - Clear Excel Cell Contents|
|Lesson 29 - Effective Excel Printing 1|
|Lesson 30 - Effective Excel Printing 2|
|Lesson 31 - Sorting in Excel|
|Lesson 32 - Hide/Show Row/Columns in Excel|
|Lesson 33 - Auto-Formats in Excel|
|Lesson 34 - Creating a Basic Excel Spreadsheet|
|Lesson 35 - Excel Charting Lesson: The Basic Excel Spreadsheet|
|Lesson 36 - Excel Worksheet Protection|
|Lesson 37 - Excel IF Formula Nesting|
|Lesson 38 - Excel Function Now/Today Formulas|
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.