[GH #2000] handsOnTable formula

Tags: #<Tag:0x00007efc6b893320> #<Tag:0x00007efc6b8930a0>

Hello, good afternoon.
I have an error with the formulas, if in a cell of the handsOnTable I enter the formula =TODAY(), the formula returns the value of the previous day, how can I solve this type of problem?
thank you so much

![image|633x88](upload://oh4Jwp1lndVcPbnTyWM7RUvBpnB.png)

import { HyperFormula } from ‘hyperformula’;
import ReactDOM from ‘react-dom’;
import { HotTable } from ‘@handsontable/react’;
import { registerAllModules } from ‘handsontable/registry’;
import ‘handsontable/dist/handsontable.full.min.css’;

// Inicialización de HyperFormula
const hyperformulaInstance = HyperFormula.buildEmpty({
licenseKey: ‘internal-use-in-handsontable’, // Verifica que la clave de licencia sea correcta
});

// Registrar todos los módulos de Handsontable
registerAllModules();

const ExampleComponent = () => {
const data1 = [
[‘prueba fecha’, 60, “2024/12/12”, ‘=(SUM(B1+C1))’],
];

return (


<HotTable
data={data1}
columns={[
{ type: ‘text’ },
{ type: ‘numeric’ }, // Cambiado a tipo ‘numeric’ para la columna de suma
{
type: ‘date’,
dateFormat: ‘YYYY/MM/DD’,
correctFormat: true,
defaultDate: ‘2000/01/01’,
},
{
type: ‘date’,
dateFormat: ‘YYYY/MM/DD’,
correctFormat: true,
defaultDate: ‘2000/01/01’,
}
]}
colWidths=‘140’
colHeaders={true}
rowHeaders={true}
height=“auto”
formulas={{
engine: hyperformulaInstance,
sheetName: ‘Sheet1’
}}
licenseKey=“non-commercial-and-evaluation”
/>

);
};

ReactDOM.render(, document.getElementById(‘example1’));

Hi @ivan.acevedo

Thank you for contacting us. I did a quick check for this formula, and it works fine: https://jsfiddle.net/handsoncode/gtzc62jy/ Can you please modify this example so the issue is replicable?

Captura%20de%20pantalla%20_ERROR_FORMULA_1 Captura%20de%20pantalla%20ERROR_FORMULA_2

The error I get is that the formula =today() always brings me the day before, I tried your exercise and I get the information as I sent it to you in the photo.
Thank you very much for your help and collaboration.
Postscript: I am located in Colombia.

@ivan.acevedo

That’s strange. If you tried the example I sent you it should work exactly the same. Can you please try the same formula in Google Sheets and Microsoft Excel and see if you also get the previous day as a result?

captura_excel captura_excel_2

in excel and google sheets it works correctly

@ivan.acevedo

Can you please also try this example and tell me what is the result? https://stackblitz.com/edit/handsontable-hyperformula-demos-ldeu5j?file=src%2Fdata.js,src%2Fui.js

Additionally, do you use any VPN services? That might affect the logic of this formula. Also, what browser and OS do you use?

this is my result,
I don’t use vpn

I’ll do some more research and get back to you after the weekend.

Hi @ivan.acevedo

Can I ask you to check what values this example returns in the console on your side? https://jsfiddle.net/handsoncode/mhn379o5/ Just check the dev console without any additional actions.

Good morning, I share the requested information with you.

Hi @ivan.acevedo

Thank you for checking it. I was able to reproduce the issue only after changing my OS time zone to Bogota, Colombia. I also reported it internally for further investigation as it seems to be a bug in our Formulas plugin. I’ll update you once we have the fix ready.

Good morning, doing a search on how to solve the error presented I found that dates in js are handled differently ____ var d = new Date(“2015-12-04T00:00:00”);
This works correctly, but if handled this way it leaves test = new Date(‘2018-12-04’)
It is when I have one day left, but the problem as such is how I could put it in the formula, since in the formula I always have one day left, I share the link where I found how the date works https://es.stackoverflow.com/questions/219147/new-date-en-javascript-me-resta-un-dia, suddenly there is a way where I can set a time zone to the handsOnTable?, to see if this way the error can be solved, many thank you

Thank you very much for your collaboration and your attention, I will be very attentive to your help, thank you very much

@ivan.acevedo

The time zone cannot be changed within Handsontable’s settings so it has to be handled at the core. I hope we will be able to investigate it soon.

Good morning,

I hope this message finds you well. I would like to kindly request an update on the progress regarding the error we reported. I’m concerned as this issue has caused a delay in the delivery of a project, and I would appreciate it if you could provide an estimated resolution date.

Thank you in advance for your attention and the support provided.

Best regards,

Hi @ivan.acevedo

The issue is in our backlog, but hasn’t been investigated yet, as we are currently finishing other projects. I’lll update you as soon as I have more information on the possible timeline for this fix.

Hello, I’ve been searching the forum for something related to this date problem. I’ve been experiencing a similar problem, but in my case it’s using any formulas. Both my Handsontable and Hyperformula instances are configured in the same locale, but even using the default, all the functions return a day earlier than they should be. In the example I use a specific date and define a date function in another cell, to add a month to the cell in relation to the original date, but instead of returning the same day of the next month, it returns the previous day.

Handsontable date problem exemple - JSFiddle - Code Playground

note that for correction, in the first EDATE function I added a day to the function’s return.

Hi @dev.gustavo.oliveira

Thank you for contacting us and sharing this solution. I’m sure it will be helpful to other users encountering this problem before we fix this on our side.