Date and time handling

The formats for the default date and time parsing functions can be set using configuration options:

• dateFormats,
• timeFormats,
• nullYear.

The API reference of dateFormats and timeFormats describes the supported date and time formats in detail.

Example

By default, HyperFormula uses the European date and time formats.

dateFormats: ['DD/MM/YYYY', 'DD/MM/YY'], // set by default
timeFormats: ['hh:mm', 'hh:mm:ss.sss'], // set by default

To use the US date and time formats, set:

dateFormats: ['MM/DD/YYYY', 'MM/DD/YY', 'YYYY/MM/DD'], // US date formats
timeFormats: ['hh:mm', 'hh:mm:ss.sss'], // set by default

Custom date and time handling

If date and time formats supported by the dateFormats and timeFormats parameters are not enough, you can extend them by providing the following options:

• parseDateTime, which allows to provide a function that accepts a string representing date/time and parses it into an actual date/time format
• stringifyDateTime, which allows to provide a function that takes the date/time and prints it as a string
• stringifyDuration, which allows to provide a function that takes time duration and prints it as a string

To extend the number of possible date formats, you will need to configure parseDateTime . This functionality is based on callbacks, and you can customize the formats by integrating a third-party library like Moment.js, or by writing your own custom function that returns a DateTime object.

The configuration of date formats and stringify options may impact some built-in functions. For instance, the VALUE function transforms strings into numbers, which means it uses parseDateTime. The TEXT function works the other way round - it accepts a number and returns a string, so it uses stringifyDateTime. Any change here might give you different results. Criteria-based functions (SUMIF, AVERAGEIF, etc.) perform comparisons, so they also need to work on strings, dates, etc.

Moment.js integration

In this example, you will add the possibility to parse dates in the "Do MMM YY" custom format.

To do so, you first need to write a function using Moment.js API:

import moment from "moment";

// write a custom function for parsing dates
export const customParseDate = (dateString, dateFormat) => {
  const momentDate = moment(dateString, dateFormat, true);
  // check validity of a date with moment.js method
  if (momentDate.isValid()) {
    return {
      year: momentDate.year(),
      month: momentDate.month() + 1,
      day: momentDate.date()
    };
  }
  // if the string was not recognized as
  // a valid date return nothing
  return undefined;
};

Then, use it inside the configuration options like so:

const options = {
    parseDateTime: customParseDate,
    // you can add more formats
    dateFormats: ["Do MMM YY"]
};

After that, you should be able to add a dataset with dates in your custom format:

const data = [["31st Jan 00", "2nd Jun 01", "=B1-A1"]];

And now, HyperFormula recognizes these values as valid dates and can operate on them.

Demo

Run calculations Reset

Release 1.0.0	Release 4.3.1	Number of days between

Source code

/**
 * Function defining the way HF should handle the provided date string.
 *
 * @param {string} dateString The date string.
 * @param {string} dateFormat The date format.
 * @returns {{month: *, year: *, day: *}} Object with date-related information.
 */
const customParseDate = (dateString, dateFormat) => {
  const momentDate = moment(dateString, dateFormat, true);

  if (momentDate.isValid()) {
    return {
      year: momentDate.year(),
      month: momentDate.month() + 1,
      day: momentDate.date(),
    };
  }
};

/**
 * Date formatting function.
 *
 * @param {{month: *, year: *, day: *}} dateObject Object with date-related information.
 * @returns {string} Formatted date string.
 */
const getFormattedDate = (dateObject) => {
  dateObject.month -= 1;

  return moment(dateObject).format('MMM D YY');
};

/**
 * Initial table data.
 */
const tableData = [['Jan 31 00', 'Jun 2 01', '=B1-A1']];
// Create an empty HyperFormula instance.
const hf = HyperFormula.buildEmpty({
  parseDateTime: customParseDate,
  dateFormats: ['MMM D YY'],
  licenseKey: 'gpl-v3',
});

// Add a new sheet and get its id.
const sheetName = hf.addSheet('main');
const sheetId = hf.getSheetId(sheetName);

// Fill the HyperFormula sheet with data.
hf.setCellContents(
  {
    row: 0,
    col: 0,
    sheet: sheetId,
  },
  tableData,
);

/**
 * Fill the HTML table with data.
 *
 * @param {boolean} calculated `true` if it should render calculated values, `false` otherwise.
 */
function renderTable(calculated = false) {
  const tbodyDOM = document.querySelector('.example tbody');
  const updatedCellClass = ANIMATION_ENABLED ? 'updated-cell' : '';
  const { height, width } = hf.getSheetDimensions(sheetId);
  let newTbodyHTML = '';

  for (let row = 0; row < height; row++) {
    for (let col = 0; col < width; col++) {
      const cellAddress = { sheet: sheetId, col, row };
      const cellHasFormula = hf.doesCellHaveFormula(cellAddress);
      const showFormula = calculated || !cellHasFormula;
      const cellValue = displayValue(cellAddress, showFormula);

      newTbodyHTML += `<td class="${cellHasFormula ? updatedCellClass : ''}"><span>
      ${cellValue}
      </span></td>`;
    }
  }

  tbodyDOM.innerHTML = newTbodyHTML;
}

/**
 * Force the table to display either the formula, the value or a raw source data value.
 *
 * @param {SimpleCellAddress} cellAddress Cell address.
 * @param {boolean} showFormula `true` if the formula should be visible.
 */
function displayValue(cellAddress, showFormula) {
  // Declare which columns should display the raw source data, instead of the data from HyperFormula.
  const sourceColumns = [0, 1];
  let cellValue = '';

  if (sourceColumns.includes(cellAddress.col)) {
    cellValue = getFormattedDate(hf.numberToDate(hf.getCellValue(cellAddress)));
  } else {
    if (!hf.isCellEmpty(cellAddress) && showFormula) {
      cellValue = hf.getCellValue(cellAddress);
    } else {
      cellValue = hf.getCellFormula(cellAddress);
    }
  }

  return cellValue;
}

/**
 * Replace formulas with their results.
 */
function runCalculations() {
  renderTable(true);
}

/**
 * Replace the values in the table with initial data.
 */
function resetTable() {
  renderTable();
}

/**
 * Bind the events to the buttons.
 */
function bindEvents() {
  const runButton = document.querySelector('.example #run');
  const resetButton = document.querySelector('.example #reset');

  runButton.addEventListener('click', () => {
    runCalculations();
  });
  resetButton.addEventListener('click', () => {
    resetTable();
  });
}

const ANIMATION_ENABLED = true;

// Bind the button events.
bindEvents();
// Render the table.
renderTable();