How to disable the automatic parsing of date/time values

[jamshaid-tariq-geekybugs]

Description

I have a hyperFormula instance.
I am adding a sheet and setting its content to array of array.

import {useEffect} from 'react';
import { registerAllModules } from 'handsontable/registry';
import HyperFormula from "hyperformula";
registerAllModules();
const hf =  HyperFormula.buildEmpty({
dateFormats: ['MM/YYYY/DD'],
 licenseKey: "internal-use-in-handsontable",
})

const data = [
["id",    "Parameter",    "Group",    "Type",    "Units",
"02-01-2021",    "02-08-2021",    "02-15-2021",    "02-22-2021",
"03-01-2021",    "03-08-2021",    "03-15-2021",    "03-22-2021",
"03-29-2021",    "04-05-2021",    "04-12-2021",    "04-19-2021",
"04-26-2021",    "05-03-2021",    "05-10-2021",    "05-17-2021",
"05-24-2021",    "05-31-2021",    "06-07-2021",    "06-14-2021",
"06-21-2021",    "06-28-2021",    "07-05-2021",    "07-12-2021",
"07-19-2021",    "07-26-2021",    "08-02-2021",    "08-09-2021",
"08-16-2021",    "08-23-2021",    "08-30-2021",    "09-06-2021",
"09-13-2021",    "09-20-2021",    "09-27-2021",    "10-04-2021",
"10-11-2021",    "10-18-2021",
"10-25-2021",    "11-01-2021"
],
["Variable-75815",    "Variable",    "-",    "Variable",    "",    "1",    null,    null,
"1",    null,    "1",    null,    null,    "1",    "1",    null,
"1",    null,    null,    null,    "1",    "11",    null,    "1",
"1",    "1",    "1",    "1",    "1",    "1"  ],
[
"Flow-94847",    "Flow",    "-",    "Flow",    "",    null,    "1",    null,
null,    "1",    null,    null,    "1",    null,    "1",    "1",
"1",    "1",    "1",    "1",    "1",    "1",    null,    "1",
"1",    "1",    "1",    "1",    null,    null,    "1"  ],
[
"Stock-99695",    "Stock",    "-",    "Stock",    "",    null,    null,    "1",
"1",    null,    "1",    "1",    "1",    null,    null,   "1",
null,    null,    null,    "1",    "1",    null,    null,    "1",
null,    null,    null,    "1",    "1",    "1",    "1"  ]
];

export const ExampleComponent = () => {
useEffect(()=> {
  let sheetName = hf.addSheet('Sheet1');
      let sheetId = hf.getSheetId(sheetName);
      hf.setSheetContent(sheetId, data);
}, []);

const getValues=() => {
 console.log(hf.getAllSheetsValues())
}

return <button onClick={getValues}>working</button>
};

ReactDOM.render(<ExampleComponent />, document.getElementById('example'));

Steps to reproduce

when I getCalculated values hf.getAllSheetsValues(). It is converting dates into timestamp i guess but dates are in string.
Below is demo, click on getValues button and check data in console. dates are changing to number. Problem is I dont have handsontable instance to set cell type. I just have hyperformula instance.
I have changed DateFormate to Us as well as Europe both are changing the date to timestamp.

Demo

https://jsfiddle.net/1uscqvL2/8/

[sequba]

Hi @jamshaid-tariq-geekybugs, thanks for reaching out to us!

Strings that can be parsed as dates according to dateFormats are automatically converted to NUMBER_DATE type. To prevent this behavior you may either:

• "force" the string type on a value by prepending it with ' (an apostrophe),

const data = [[
  //...
  "'02-01-2021",
  //...
]]

• remove the dateFormats completely. Be warned that this would effectively disable the time-handling features in HyperFormula and may cause some other unexpected issues.

const hf =  HyperFormula.buildEmpty({
  licenseKey: "internal-use-in-handsontable",
  dateFormats: [],
})

Alternatively, you may let HyperFormula store the dates in NUMBER_DATE type and then convert them to strings using the TEXT() function.

const data = [[
  "02-01-2021",
  "=TEXT(A1, "MM-DD-YYYY")"
]]

Please, let me know if one of these methods solves your problem.

[jamshaid-tariq-geekybugs]

@sequba , A workaround before your answer was to use any unexpected dateFormats like dateFormats:['MM/YYYY/DD']. I think this will never happen in real life so it resolved my issue.
As I cannot change data so method 2 from your answer is best for my scenario dateFromats: [] . seems like it resolved the issue but I dont know if both of above methods will give any unexpected value in other cells. It needs testing. what do you think which method is best from dateFormats:['MM/YYYY/DD'] and dateFromats: [] ?

[sequba]

If your data does not include strings in the form of MM/YYYY/DD, MM-YYYY-DD, MM.YYYY.DD or MM YYYY DD, these configurations should be equivalent, but - to be honest - HyperFormula was never tested with dateFromats: [] so I cannot be certain about it ;)

[jamshaid-tariq-geekybugs]

yeah Data won't includes dates in this format. I think dateFromats: [] looks cleaner.. Thank you.

[ndnenkov]

@sequba I would like for cells that can be interpreted as dates (based on dateFormats) to indeed be treated as dates. But I eventually want to export the string values when the user is done editing.

Is there a way to do that? Like is there a way when I call getSheetValues to know that a value is a NUMBER_DATE so I can TEXT-ify it or something like that?

[sequba]

@ndnenkov The conversion from date value to a string can be done using the TEXT() function as mentioned above.

The getSheetValues method doesn't stringify dates automatically. but you can implement a custom version of it according to the pseudocode:

sheetValues = hf.getSheetValues()
foreach (cellAddress, cellValue) of sheetValues:
  if hf.getCellValueDetailedType(cellAddress) == NUMBER_DATE
    cellValue = hf.calculateFormula(`=TEXT(${cellAddress}, "mm/dd/yyyy")`)

You may need to use simpleCellAddressToString helper method for address conversion.

Does it solve your issue?