Excel stores dates in a very clever way. Understanding Excel's date storage system empowers you to use date arithmetic.
You can use date arithmetic to compute the difference between two dates in days, or to shift date ranges by a given time interval.
Let's first look at how Excel stores dates. Dates are stored as simple numbers called Date Serial Numbers. The serial number contains the number of days that have elapsed since the 1st of January 1900, where the 1st of January 1900 is Day 1.
An interesting shortcoming of Excel is its inability to easily work with dates before 1900. Excel simply doesn't acknowledge that there were any dates before 1900. So if you work with older dates you'll have to work around this limitation.
Look at the value in cell A6, the Date Serial Number 1. This equates to the 1st of January 1900 at midnight.
Next you need to realize that, in Excel, every time is a date and every date is a time. This one's an eye opener. We've already realized that the 5th of January 1900 is stored as the Number 5. What would the Number 5.5 mean? It would mean midday on the 5th of January 1900.
It's possible to format a date to show only the date, only the time, or both a time and a date. When you enter a time into a cell without a date, the time is stored as a number less than 1. Excel regards this as having the nonexistent day of the 0 of January 1900. You can see that the Date Serial Number in cell A16 is 0.5. That doesn't relate to any date at all, so Excel regards this date as the 0 of January 1900. But it does relate to a time: the time 12:00 midday.
Now that you broadly understand how Date Serial Numbers work, let's see them in action by creating a test worksheet. I'll click the File button and then the New button, and then I'll open a blank workbook.
I'll type the Number 1 into cell A1 and then I'll use the skills that you learned in Lesson 2-14 to AutoFill sequential numbers into cells A1 to A5. To do that, I click on cell A1, I right click on the AutoFill Handle and drag down to cell A5, and then, when I release the Mouse button, I select Fill Series from the Shortcut Menu.
In cell B1 I'll use the skill that you learned in Lesson 2-13 to enter a formula that will echo the number in cell A1. So I'll type = on the keyboard, click on cell A1, and then press the Enter key. And now I'll AutoFill that formula down to the end of the range. So I'll click on cell B1 and then double click on the AutoFill Handle.
I'm sure you can now understand that any number I enter in Column A will now be repeated in Column B. Let's change A2 to 5 and you can see that now B2 is also 5. And I'll change A2 back to the number 2 again and cell B2 also changes to the number 2. The reason I've done this is so that I can enter some Date Serial Numbers in Column A and you'll see the corresponding date and time in Column B. After, of course, I've formatted Column B so that it shows dates and times.
I'd now like to reformat the numbers in Column B so that they show dates and times. And I'd like to show the dates and times in a rather special way. I want the dates to have four digits, so that I can see the difference between 1900 and 2000. And I'd like the times to follow the 24 hour clock system, so that I can tell whether the time is AM or PM. So I'll select the cells in Column B and then, using the skills you learned in the previous lesson, I'll format them as dates.
So I'll right click in the selected cells and select Format Cells from the Shortcut Menu. You can see that, at the moment, the values in Column B are being shown as simple numbers, but I want dates and times. And I want dates and times in a special format so I can see four number dates, and 24 hour times. So let's click on the Date category to see if there's a suitable quick format.
Well I can see that there isn't at the moment and, as explained in the previous lesson, these quick formats are different depending on your locale.
I can see that the locale is currently set to English (United Kingdom). So let's try English (United States) and see if there's a suitable format here. Well there's one that's almost suitable: "3/14/12 13:30", but the year only has two numbers and I wanted four.
When you need a very special format like this one you're going to have to create your own custom format. So I'll click Custom to see if I can do that.
Now as you can see, the Custom Formats are quite cryptic, but with a little detective work we should be able to figure out how to create the custom format we need. I need to look at the Sample box at the top of this dialog as I click on each option.
Let's try the first one. Well there's my four digit year and I can see that that's coming from the Custom Format "yyyy".
Let's try the next one. There's my three letter month and it's coming from the format "mmm".
And now let's look at some of the times. I can see "h:mm AM/PM". Well that's going to show the AM/PM time notation, and that would work but I'd rather see a 24 hour time.
Here I have another AM/PM notation and here I have a single digit for the hour. I think I now have enough information to guess what my custom format's going to be. I think it's going to be "dd-mmm-yyyy", and that's certainly giving me the correct date in the Sample Box.
Now I'll press the Spacebar and enter "hh:mm", and that seems to have worked too. So I can click OK and all of the cells in Column B now have a four digit year and a 24 hour clock time.
I can see also that the numbers 1, 2, 3, 4, 5, as expected, relate to the Date Serial Numbers for the 1st of January 1900 to the 5th of January 1900. If I was to enter a very large value in cell A1, say 2,500 I'd expect to see a date that was 2,500 days later than the 1st of January 1900.
I'll press the Enter key now, and then I'll resize the column. And you can see that 2,500 days after the 1st of January 1900 was the 4th of November 1906.
Let's now try a time. In A2 I'll type 2.5. When I press the Enter key, I'll expect to see the time 12:00, still on the 2nd of January 1900. I'll press the Enter key now, and I can see that that's exactly what the Date Serial Number 2.5 relates to.
Let's try another one now with 3.75. For this, I'll expect to see a time that's 3 quarters of the way through the 3rd of January 1900. That would be 6:00 in the evening or, to put it another way, 18:00 hours. So I'll press the Enter key and you can see that that's also exactly correct.
Now let's do some date arithmetic by computing the number of days that occurred in the 20th century. So in cell B7 I'll type the 1st of January 1900 and in cell B8 I'll type the 1st of January 2000. Even though cells B7 and B8 are displaying dates you know that, behind the scenes, Excel is storing them as the number 1 in cell B7 and a number that relates to the number of days in the 20th century plus 1 in cell B8.
So let's enter formula in B9 to work out the number of days in the 20th century. I'll press = on the keyboard, click on the 1st of January 2000, then - on the keyboard, and I'll click on the 1st of January 1900.
I'll now press the Enter key, and I can see that there were 36,525 days in the 20th century. There's no need to save this workbook so I'll now close Excel without saving.
And you've now completed Lesson 4-2: Understand Date Serial Numbers.