Date timezone issue

[PRR24]

If cell value is set to new Date(2017, 2, 15) as in example, it will result date value 03/14/2017 22:00:00 in Excel file. I guess, the diff is connected to my timezone. Also if I set cell value as new Date(), it will result a time two hours back. I would expect to see a result 3/15/2017 00:00:00 for the first and current time for the second case in Excel.

[philippgerbig]

same here....

[mmoczulski]

Found workaround for it. I'm not quite happy with it, but at least it worked for my case :wink:

const moment = require('moment-timezone');

const now = moment().tz('Europe/Zurich'); // or whatever timezone you're interested in
const date = now.add(now.utfOffset(), 'minutes').toDate();

This makes a new date object with hour value of desired timezone. Keep in mind, it's still in UTC.

[funklos]

When working with vanilla Date objects you could try the following: #538 new Date( Date.UTC( date.getFullYear(), date.getMonth(), date.getDate(), date.getHours(), date.getMinutes(), date.getSeconds() ) )

This results in a localised date object. This can then be passed to exceljs and formatted with the style attribute of a cell or column like this:

const EXCEL_FORMATS = { DATE: { numFmt: 'dd.mm.yyyy' }, DATE_TIME: { numFmt: 'dd.mm.yyyy hh:mm' }, TIME: { numFmt: 'hh:mm' } }

The visible cell value will be formatted according to the numFmt you used, but behind the scenes the value will be of type Date, retaining also the time even when you format the cell with the EXCEL_FORMATS.DATE format.

[padinko]

if you use moment, you can do:

moment(date).utcOffset(0, true)

to get same date and time in UTC time zone

[peterpeterparker]

Thx for the workaround @mmoczulski 👍

The same approach, but with dayjs, did the job for me too:

const myDateLocalTime = dayjs(myDateUTC).add(dayjs().utcOffset(), 'minute').toDate();

[PRR24]

Closing the issue as using UTC may be initially unexpected, but still a valid implementation and for local time zone use workaround have been provided.

[hfhchan]

If you don't want to use a library, just use new Date('2017-03-15'). This will ensure that the Date object generated corresponds to 00:00:00 in the UTC timezone, and hence show up correctly in Excel.

According to MDN for Date.parse:

When the time zone offset is absent, date-only forms are interpreted as a UTC time and date-time forms are interpreted as local time.

[gius]

This might help someone

/**
 * Converts a date time so that the time is adjusted by the local offset and thus its UTC time matches the original local time
 */
export function convertToLocalTime(date: Date | undefined | null) {
  if (!date) {
    return date;
  }

  // If the time is midnight, it is probably date-only and we don't need to adjust the time to avoid "around midnight" issues
  const isMidnight =
    date.getUTCMilliseconds() === 0 && date.getUTCSeconds() === 0 && date.getUTCMinutes() === 0 && date.getUTCHours() === 0;
  if (isMidnight) {
    return date;
  }

  const newDate = new Date(date);
  newDate.setMinutes(newDate.getMinutes() - newDate.getTimezoneOffset());
  return newDate;
}

[otmanet]

Solution for this issue you can parse date to timeStamps following this code: 💯

  const convertDate = Date.parse("your_date")

The second step when exporting Excel, convert timeStamps to Date to keep the same date, for people who use (Exceljs): 💯

  const dateColumn = worksheet.getColumn("your_row_name"); 
   
           dateColumn.eachCell((cell: any) => {
                 cell.value = new Date(cell.value); 
                });

[ctheiss]

The workaround solution using moment has been deprecated. Here's the incantation using its successor Luxon:

import { DateTime } from 'luxon';
...
// Gets the current time (in your local time zone), and then switches the time zone
// to UTC *without* adjusting the underlying timestamp.  Note that this adjusted
// date is no longer "now" (unless you live at the same longitude as Greenwich),
// and so you likely can't perform useful math against it.
worksheet.getCell('A1').value = DateTime.now().setZone('utc', { keepLocalTime:true }).toJSDate();

[cedx]

We don't need external libraries. Convert any date to UTC timezone (aka "Use The Platform"):

// This date uses your local timezone.
const date = new Date;

// Convert it to the UTC timezone.
const utcDate = new Date(date.getTime() - (date.getTimezoneOffset() * 60_000));

I'd still prefer ExcelJS to manage timezones itself.

Note: getTimezoneOffset() returns the offset of your local timezone, NOT the timezone offset of the date object.