1

I have a google sheet which has date in DD-MM-YYYY HH:MM format. But when I download it and try to uplaod. I am getting a weird value (this value:44787.70138888889). Few things that I noticed are that after downloading the file when I open it in my pc its format changes to DD-MM-YY HH:MM. I am using Ubuntu 20.04.3 LTS and my code is in nodejs. I am using multer to upload the file and moment to convert the dateTime in desired formats. This issue arrives before any type of formatting is applied on the dateTime fields.

I logged the values that were read before any formatting and I got this :

{

'Route Code': 'testValRoute',

'Consignor Code': 'testConsignor',

'Start Date(format - DD-MM-YYYY HH:MM )': 44787.70138888889, ----> This should be "14-08-2022 20:00"

'Vendor Code': 'testName',

'Vehicle Number': 'testVehicle',

'Vehicle Type': 'testType',

'Driver Name': 'testDriver',

'Mobile Number': testPhoneNumber

}

Hamza Shaikh
  • 41
  • 4

1 Answers1

1

In Excel, date/time stamps are represented by floating point numbers. They are the numbers of days and fractions of days since 1899-12-31 (31-Dec-1899). These dates, in the weird language of Excel, are called Serial Numbers.

For me this 44787.70138888889 value translates to 2022-08-14 16:50, so you have reasonable values.

To change a Serial Number into a Javascript timestamp, use this function.

function date2ms(d) {
  let date = new Date(Math.round((d - 25569) * 864e5));
  date.setMinutes(date.getMinutes() + date.getTimezoneOffset());
  return date;
}

Explanation here. Converting Excel Date Serial Number to Date using Javascript

Here is an interesting personal account of this situation.

O. Jones
  • 103,626
  • 17
  • 118
  • 172