Level 2 - Lesson 11 - Dates and Times

Category: [General,Excel] Demo Available

Dates and Times

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 File>Options and on the Advanced dialog, check use 1904 date system under When calculating this workbook. 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-2008 in any cell. Now format the cell as a General number format, it will display the value 39801. This simply means that 19-Dec-2008 is exactly 39801 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-2008 example and modify it to also include a time, let's say 12:00. We would enter this into a cell as: 19-Dec-2008 12:00. If we now format this cell as General we will see: 39801.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 entered the hours 8:00 , 8:00, 10:00, 7:00, 7:30 respectively. (Note that when you enter the hours and use the colon : the cells will automatically be formatted as h:mm)

Lets say 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 1:30:55 PM or a Custom format of [h]:mm:ss. We then get our expected result of 40:30:00. If you only wanted to show hours and minutes you could use [h]:mm:ss.

Once you understand Excels method of storing dates and times it should no longer be a mystery on how Excel uses them in calculations. While it is a very simple method it is also very effective. Problems and confusion can be run into when using Times as well. These will be discussed in detail later.

When you Need Help

No matter how proficient a person is in using Excel, there will be times when you need to get help. Excel has a huge help file, it offers help on each and every aspect of Excel. While this is needed, the size of the help file can often seem overwhelming or make it very hard to find help on the topic you want. Most people often snub their nose at trying use the Help system. Don't become one of them as this help is always on hand and once you learn more it becomes easier to get straight to where you want to go. The easiest method to get help is to click the question mark at the top right hand side of your screen, then typing in your query in the Search box. You have the option of whether to search within the Microsoft Knowledge Base (online) or through the Help section of your computer).

Once you get into the habit of using this help system in your preferred way, you will be able to find exactly what it is you want every time with minimal effort.

Summary

While Excel is without doubt the number one spreadsheet package in the World today it can do a lot more than just crunch numbers. It can easily manipulate text just as well. Most users of Excel unfortunately never come to realise anywhere near its full potential. Even though Excel can be found in nearly every office worldwide most are using it for no more than a big notepad with a few basic formulas in it.

Always keep in mind the three vital aspects of Excel, the Workbook, Worksheet and the Cell. Once you are in Excel, the cell becomes the very backbone of all that you do. Always keep in mind that there are many more rows than there are columns and set up all spreadsheets accordingly.

When starting a spreadsheet take the time to think it through and plan, if you get the foundations correct everything else will flow on. Adhere as much as possible to the 6 rules, it may mean a bit of short-term pain, but believe me this will be outweighed by the long-term gain.

Use range names where possible and define constant values. This may seem a bit more time consuming initially, but it will make life a lot easier further down the road.

Familiarise yourself with how Excel interprets dates and times. While not every spreadsheet uses them, most do, or they at least use dates. Excel itself recognises the fact a lot of spreadsheets will involve dates and times and so has numerous date and time functions available to the user. All of these functions rely on the serial numbers of dates and the decimal fractions of times and quite often the combination of the two.

Last but by no means least, use the help often so you will understand the way in which it works. Once you have found the answer to a problem that is complex, make use of the Annotate feature and you will thank yourself later.

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.

Index to Excel Level 2 Lessons and Index to COVID-19 Charting examples

Gallery

stars (0 Reviews)

Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
OzGrid is in no way associated with Microsoft.

Website designed by Astrava Solutions Ltd.