Yearfrac and related functions

This page provides raw data for determining the actual spreadsheet functions' definitions. In particular:

• YEARFRAC Incompatibilities between Excel 2007 and OOXML (OXML), and the Definitions Actually Used by Excel 2007 [OpenDocument version]
• README - basic info
• calc_yearfrac.py - program (in Python2) to calculate YEARFRAC values and compare with expected values. Thus, this has embedded in it the algorithms that are actually implemented by Excel 2007 for YEARFRAC.

  To run, it requirs a file named yearfrac_data_basis.txt in the same directory, with the expected values in "version 2" format. To create such a file, extract the yearfrac_data_basis_all.zip file. I normally create a symbolic link to the right file, e.g.,

    ln -s yearfrac_data_basis1.txt yearfrac_data_basis.txt
  

  You can also concatenate all the files from the "zip" file to create a single file with the whole test suite (it will take a while to run). At the end of the run it will print the number of correct and incorrect results; during its run, it will print ERROR with every error.
• yearfrac_data_basis_all.zip, YEARFRAC values from Excel 2007, for basis 0 through 4. BIG, about 180 Megabytes. Each basis value is in a separate file with 6.5 million test cases each, for a total of about 32.9 million test cases.

  These are in "version 2" format; each line just has space-separated values for date1 (YYYY-MM-DD), date2 (YYYY-MM-DD), basis, and expected value. There are no line duplicates (though if the numbers appear different they duplicate, e.g., 0 vs. 0.00000000; this is harmless). Year 1900 is included; you probably want to ignore them.

• Spreadsheets showing specific test values being calculated: basis0.ods, basis1.ods. These were used to create calc_yearfrac.py.
• csv2flat.py - converts a 2x2 CSV matrix into the flat "version 2" format. Here are a bunch of CSV files that were used as input to this: basis_0.zip basis_1.zip basis_2.zip basis_3.zip basis_4.zip

You can also view my home page.