Date timezone error

[p3pp8]

Hello, with latest version of the module cell dates are converted not considering the timezone, ie: worksheet.date(7:00+01:00) converts to 6:00. Any help is really appreciated, i'm using strings as a workaround.

Cheers

[KudosAbhay]

Hi,

I am not sure if my issue is the same, but seems pretty same: My Original DateTime value is : 2018-03-22 12:28 Now, I want to Store it in Excel in format of: numberFormat: 'hh:mm dd/mm/yyyy' However, I am getting it stored as: 06:58 22/03/2018

Can you guys help me out ?

[natergj]

The best way to guarantee your date string is to send a UTC date string ws.cell(1,1).date('2018-03-22T12:28:00.0000Z')

From section 18.17.4.1 of the spec guide

A date that can be interpreted as a numeric value is a serial value. This is made up of a signed integer date component and an unsigned fractional time component. Going forward in time, the date component of a serial value increases by 1 each day. A serial value represents a UTC date and time, and, as such, has no timezone information.

That no timezone information bit the difficult thing because Javascript DateTime objects do have timezone and reconciling the two is not a straight forward task. Using UTC date strings in your code should show everything the way you like.

[chk-]

Hi, I worked arround this issue by manually adding the timezoneOffset to the date:

function correctTimezoneIssue(jsDate) {
      return new Date(jsDate.getTime() + (jsDate.getTimezoneOffset()*-1) *60000);
}

ws.cell(rowNo, colNo).date( correctTimezoneIssue( new Date( "2018-05-15T12:32:33.248Z" )) );

[natergj]

@chk- you're likely to still run into issues with this solution if you live in an area that observes daylight saving time. The date string that gets created will have the offset based on the date that you pass into the function.

new Date( "2018-05-15T12:32:33.248Z" ).getTimezoneOffset() = 300 new Date( "2018-01-15T12:32:33.248Z" ).getTimezoneOffset() = 360

You'll probably see that dates created display fine as long as they match the daylight saving period you're currently in, but dates created outside that period will show as an hour off.

[chk-]

@natergj Thank you for your advise, you're totaly right. So I went deeper and these are the facts I could collect: Excel treats dates as universal. I generated a file in London and opened it in Arizona (-7). -Date/Times are showing the same values. The consumer of my app defines a time. When the consumer exports the data into Excel, he awaits the same time showing up in the cell as he defined it in the app. So the consumers timezone has an important impact. The nature of a server side computation adds the difficulty on this task. -The generating library needs to know the local timezone-offset... This is something that I have to think about.

Now back to the fact, that there is a serious issu with the function getExcelTS. If I use

ws.cell(rowNo, colNo).date( "2019-03-31T00:00:00.000Z" );

The cell will show 30.03.2019 23:00:00. (I have UTC +1). This issue I work around by using the prooved and stable function

function datenum(v, date1904) {
  if(date1904) v+=1462;
  var epoch = Date.parse(v);
  return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000);
}
ws.cell(rowNo, colNo).number( datenum("2019-03-31T00:00:00.000Z") )

For situations, where only a date is needed, the "datenum" solution works as expected. For situations, where the cell value must match the time displayed in the app at the user's computer, there still remains the timezone-offset issue.

[iamjoyce]

The best way to guarantee your date string is to send a UTC date string ws.cell(1,1).date('2018-03-22T12:28:00.0000Z')

@natergj Is it right to say that this is a temporary solution?

[nivb52]

I found the same error and fix it on v1.7 by kind of this solution in setting up the UTC. If you will send me the relevant file, line in this version i will check if it will work for this version as well

[mactyr]

In case anyone else is here because you're seeing date shifts by 1 hour that might be related to daylight savings time changes somehow having an effect on the value UTC timestamps -- that's under discussion at #324.