Over the many years I have been using and training people to use Microsoft Excel, one area stands out and seems to cause confusion - that is using dates and times.
Key to understanding how Excel uses dates and times is to appreciate that it represents a date by a serial number and a time as a fraction of a day (expressed as a decimal fraction). For example, the date 12/8/1998 would have a serial number of 36019. 3pm for the 12/8/1998 would have a serial number of 36019.625 and 7am for the 12/8/1998 would have the serial number 36019.2916666667 (0.625 being the fraction of the day gone by 3pm etc.) Excel provides methods of displaying these date serial numbers in formats understandable to humans.
Excel is clever. It recognises when you enter a date in a compatible format (ie 12/7/98). Depending on the system settings in your particular installation of Excel, you will see on screen something like 12/07/1998 or 12 July 1998 but, behind the scenes, this date has been stored as a number (in this case 35988).
By now you might be wondering why 35988? Where has this number come from? By default, Excel allows you to start using dates from 1 January 1900 (ie serial number for 1/1/1900 = 1, 3/1/1900 = 3 and so on). You cannot perform calculations using dates before 1 January 1900. There is also an upper limit for dates too - 31 December 9999 (MostExcel users are not inhibited by this upper limit!).
As you look at options available with Excel 2010, you may see the option to use the 1904 date system. What is all this about? This allows compatibility with some early Macintosh systems which could not support dates prior to 1 Jan 1904 (all to do with the year 1900 not being a leap year) so serial numbers for these systems started in 1904 and not 1900.
More to come about dates and times in Excel to follow in a future post.
No comments:
Post a Comment