The tutor discusses Excel’s conversion of dates to serial numbers.

In financial calculations, the time duration is often important. Consider the following example:

Calculate the duration from April 27, 1988 to March 16, 2016.

Mathematically, the problem can be thought of as

March 16, 2016 – April 27, 1988.

The typical date format may not be the most convenient for such calculation.

Excel sees the calculation as follows:

42445 – 32260

The number 42445 is Excel’s serial number for the date March 16, 2016; similarly, 32260 corresponds to April 27, 1988. The serial number of a date is the number of days from the turn of 1900 to, and including, that day. (Apparently, Excel in Macintosh starts the serial number from the turn of 1904.)

42445 can be confirmed as the number of days up to, and including, March 16, 2016, since the turn of 1900:

75×365 + 26×366 + 12×365 +3×366 + 31 + 29 + 16 = 42445

To continue with the example above:

March 16, 2016 – April 27, 1988 = 42445 – 32260 = 10185 days

The corresponding number of years is 10185/365 = 27.9.

Excel has built-in formulas for converting dates. I’ll be discussing some of them in future posts:)

Jack of Oracle Tutoring by Jack and Diane, Campbell River, BC.