Date issue

[bharadipankaj]

Hello,

I am facing issue in date. Sometime +1 day and -1 day date i got.

My Server is in Australia and we accessing Server from Singapore and India. I am using Moment js for formatting. E.g. let dbDate="2018-10-15T11:00:00.0000Z"; let formattedDate=moment(dbDate).format("YYYY-MM-DD"); console.log(formattedDate) // Shows correct date.

ws.cell(10, 1) .date(formattedDate) .style({numberFormat: 'MMM-YY'});

// above code print wrong date in excel +1 or -1 day

Please help me asap.

Thanks in advance.

[natergj]

The issue here is that you are using moment to format your date prior to sending it to the cell's date setter. Dates in Excel are really just a unit difference from an arbitrary point in time and timezones aren't really a thing. The easiest way to think about this is to work under the impression that your Excel workbook will always display in UTC time and the date that you put into cells should be UTC time. If you take out the lines to format your date via moment, you will get the consistent results that you expect.

[bharadipankaj]

Hi, I wanted to show only date without time. I am using your style for formatting the date field, But it just hide time,if you see the actual value it shows time. Do you have any support for functions or macro? Or any another way to show only date? Please help me as early as possible.

[bharadipankaj]

Hi Generly i face date +1 or -1 in the case of Day light saving case, Our server is in Australia. So could you help me asap on this.

Thanks

[natergj]

@bharadipankaj, If you create a date using a string value with the time set explicitly to midnight (i.e "2018-12-08T00:00:00.000Z"), Excel should display the date as only the date. This will display as just the date in the cell and also when clicking on the cell.

const xl = require('excel4node');
const wb = new xl.Workbook();
const ws = wb.addWorksheet('Sheet 1');
ws.cell(1, 1).date("2018-12-08T00:00:00.000Z");
wb.write('Date.xlsx');

[natergj]

Here is a quick date cleaning function that may help you get to correct date strings for what you are trying to do.

function getUTCMidnightDateString(date) {
  const dt = new Date(date);
  dt.setHours(0);
  dt.setMinutes(0);
  dt.setSeconds(0);
  dt.setMilliseconds(0);
  dt.setMinutes(dt.getMinutes() - dt.getTimezoneOffset());
  return dt.toISOString();
}

// From Node console
> const mongoDate = new Date();
undefined
> mongoDate
2018-12-08T14:59:33.886Z
> getUTCMidnightDateString(mongoDate)
'2018-12-08T00:00:00.000Z'