Standardize Cell Formats

In Microsoft Excel you can format cell values. For example, the date "15/09/2021" could be formatted as "15 September 2021" or "2021-09-15". By default, KeyView extracts cell values with formatting, as they would appear in Excel. If you prefer, you can configure KeyView to standardize cell values.

To standardize cell formats

  • In the C API, call the function fpSetConfig and set the flag KVFLT_STANDARDIZECELLFORMATS.
  • In formats.ini, set the following parameter. (This is an alternative approach - you do not need to do this if you have configured this feature through the API).

    [Options]
    StandardizeCellFormats=TRUE

When this feature is enabled, KeyView formats any cell where a number has been entered according to the following rules.

Numbers

Numbers are printed to the maximum length entered–that is, the full number put into the cell, without any rounding. Negative numbers are printed with a dash in front of them (as opposed to, for example, bracket form).

The following table provides some examples.

Example Formatted value KeyView (standardized) output
Rounded number 600 600.1
Scientific notation 1.56E+04 15600
Fraction 17/20 0.85
Percentage 46% 0.46

Text

All text that is part of the format string is stripped, including currency symbols.

Dates

All dates are printed in full ISO-8601 format (that is YYYY-MM-DDTHH:MM:SS). There are two exceptions to this rule:

  • Cases where the date format contains a time delta (that is, "[h]", "[m]", or "[s]"). In this case, the time is displayed as an interval, which is the number of days (where a day is defined as a period of 24 hours). The time is printed in the ISO-8601 time interval form, for example P1.234D.
  • Cases where the absolute value of the cell is less than 1.0, and the date format contains only time components. In Excel, values between 0.0 and 1.0 correspond to the fictional date 1900-01-00, and are used to express times without an associated date. For example:

    Value Date format KeyView output
    0.5 hh:mm:ss 12:00:00
    0.5 dd hh 1900-01-00 12:00:00
    1.5 hh:mm:ss 1900-01-01 12:00:00
    1.5 dd hh 1900-01-01 12:00:00