We all have to track our time; that's generally a given if one wishes to get paid. The time tracker in one of my projects using FogBugz recently changed their format from a 100-based system to the time-based :00 to :59 seconds format (eg. instead of showing I worked 7.5hrs on given day it's now showing 7:30), which is not how Freshbooks tracks their time and thus how I send my invoices.
As I searched for a handy way to convert times between these formats, I ran across this post by Chip Pearson on an Excel forum which had a useful answer. Looking further into his original article I found this particular passage incredibly interesting (emphasis mine):
Excel stores dates and times as a number representing the number of days since 1900-Jan-0, plus a fractional portion of a 24 hour day: ddddd.tttttt . This is called a serial date, or serial date-time.
The integer portion of the number, ddddd, represents the number of days since 1900-Jan-0. For example, the date 19-Jan-2000 is stored as 36,544, since 36,544 days have passed since 1900-Jan-0. The number 1 represents 1900-Jan-1. It should be noted that the number 0 does not represent 1899-Dec-31. It does not. If you use the MONTH function with the date 0, it will return January, not December. Moreover, the YEAR function will return 1900, not 1899.
Actually, this number is one greater than the actual number of days. This is because Excel behaves as if the date 1900-Feb-29 existed. It did not. The year 1900 was not a leap year (the year 2000 is a leap year). In Excel, the day after 1900-Feb-28 is 1900-Feb-29. In reality, the day after 1900-Feb-28 was 1900-Mar-1 . This is not a "bug". Indeed, it is by design. Excel works this way because it was truly a bug in Lotus 123. When Excel was introduced, 123 has nearly the entire market for spreadsheet software. Microsoft decided to continue Lotus' bug, in order to be fully compatible. Users who switched from 123 to Excel would not have to make any changes to their data. As long as all your dates later than 1900-Mar-1, this should be of no concern.
I love it - here's a great example of "let's not fix something that's broken", and instead continue it into perpetuity. Plus it's an interesting perspective into why some bugs are introduced; indeed, in this case one could argue it's not a bug at all since it was intentionally made incorrect to be compatible with a 3rd party system.
And please - before I get any critical responses, of course I am sure there was a sufficiently strong business case why they continued this bug. Which is also why I much prefer to stay in QA.
