 |
|
|
|
Dates and times are a very important part of a lot of spreadsheets and as such it is very important you understand how Excel interprets them. Excel for Windows uses the 1900 date system. Excel for the Macintosh uses the 1904 date system. The 1900 system can be changed by going to Tools>Options\Calculation and checking the 1904 date system. If you are using Windows there is no need to change this.
The 1900 date system starts from 1 January 1900 and has a numeric value of 1. This is how Excel sees dates, as numbers. You can see this by typing the date 19-Dec-2004 in any cell. Now format the cell as a General number format, it will display the value 38340. This simply means that 19-Dec-2004 is exactly 38340 days from 1-Jan-1900. By using this method Excel can perform calculations on dates. The term that is used to describe these numbers is serial numbers.
We now know that Excel uses what is known as serial numbers to store dates. It also uses a very similar system to store times. Instead of using serial numbers, Excel uses what is known as decimal fractions. In Excel 24 hours is equal to the whole number one. 12 hours is equal to 0.5 and six hours equal to 0.25. So Excel stores times as a portion of one, with one being equal to 24 hours. Again you can see the decimal fraction of a time by typing any time in any cell and formatting it as General.
To now combine this we can see how Excel would interpret a date and time in a cell. Lets stick with the 19-Dec-2004 example and modify it to also include a time, let's say 12:00. We would enter this into a cell as: 19-Dec-2004 12:00. If we now format this cell as General we will see: 38340.5 with .5 representing the time portion.
Once you understand Excels method of storing dates and times it should no longer be a mystery on how Excel uses dates and times in calculations. While it is a very simple method it is also very effective.
A common problem that people run into when working with times is when they need to use hours greater than 24. Let's say in cells A1:A5 you have the hours 8:00 , 8:00, 10:00, 7:00, 7:30 respectively. These hours represent the hours worked in one week for an employee. We need to know the total hours worked for that week, so in cell A6 we put: =SUM(A1:A5). You will see that we do not get the result we expected, we end up with: 16:30. Why does Excel do this? Well when you use a formula that references other cells our result cell will automatically take on the format of the cell(s) it is referencing. Excel sees that we have times in cells A1:A5 and so assumes we want the same in our result or total cell and so formats it as h:mm. More often than not Excel gets it right, but as you can see in this instance it hasn't. But why the result of 16:30? Lets step through this to explain why.
If you add A1 and A2 (8:00+8:00) together you get 16:00.
- If you now add this 16:00 to cell A3 (10:00) you don't get 26:00 you actually get 2:00.
- What happens is, if you add 8:00 of the 10:00 hours to 16:00 you would get 0:00 (Midnight). If you then add the remaining two hours you of course end up with 2:00, the result we got initially.
So we now know that when Excel adds 8:00+8:00+10:00 we get 2:00
- If we now add the remaining times 7:00 + 7:30 we get 14:30.
- Finally we add 2:00 to 14:30 and we get the result that Excel got, ie; 16:30
Ok this is all fine, but we don't want that, we want to force Excel to keep going once it reaches 24:00 hours. We can do this by using a Time format of 37:30:55 or a Custom format of [h]:mm. We then get our expected result of 40:30:00.