The tutor continues about Excel’s handling of dates.

I mentioned in my March 16 post that Excel can convert a date into a serial number for convenient calculation of duration between dates. The serial number is the number of days from the turn of 1900 (possibly, on Mac, from the turn of 1904) up to, and including, the date of interest.

Excel’s built-in function for converting the date to a serial number is DATEVALUE, found by clicking the Formulas tab, then Date & Time. The user can also just type =datevalue() in the formula box.

The human form of date, such as “March 20, 2106” or “Mar-20-2016” or “20/03/2016”, is called a string. DATEVALUE converts a date from a string to a serial number. Caution: DATEVALUE only accepts certain forms of date string. Here are two forms that do work:

=datevalue(“20/03/2016”) should return 42449

=datevalue(“20-Mar-2016”) should return 42449

To use one of the formulas as above, it needs to be typed exactly that way: the quotes are needed.

I’ll be talking more about spreadsheets in future posts:)

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