How do I get a string value of the 5 digit serial date format from a cell into my script?

Question

I have a formula in a cell that is doing a vlookup to another sheet by combining the values of two cells on my spreadsheet. It's a table of names on the left and dates up top. I'm combining the name and date values into a single string value and using that as by vlookup key value, which works great. Now I need to basically do the same thing in my google script, but the date value has me at a loss. In the vlookup cell formula, the date value is the 5 digit date serial code. However when I combine the two cell values in my script, I cannot get the same 5 digit serial code out of the cell. I have tried all conversions of number, string, text, ect. What function can I use to get this 5 digit date value as a string in my script?

function serialDatevalue(){
  var WB = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var NewDate = new Date();
  var NewDate =  WB.getRange('B1').getValue();
  var JSdate =  WB.getRange('B1').getValue();
  Browser.msgBox(Number(NewDate));

// Some others that I have tried
//  var JSdate = Date.parse(DateValue);
//  var JSdate = Number(DateValue);
//  var JSdate = Utilities.formatDate(DateValue, 'PST', '%');
}

for example, April 13, 2019 = 43568. I want to get this 5 digit value as a string.

I keep getting 'Sat Apr 13 2019 00:00:00 GMT-0700 (PDT)', or other longer integer values.

Please help.

Rubén · Answer 1 · 2020-06-21T18:18:50.003

From Tanaike's answer to Getting the number equalent of duration using GAS

var serialNumber = (dateObject.getTime() / 1000 / 86400) + 25569; // Reference: https://stackoverflow.com/a/6154953

The easier way to get the serialized number corresponding to a certain cell date value in Google Sheets is by using a the built-in Google Sheets function [TO_PURE_NUMBER][1]. This because Google Sheets and JavaScript, which is used by Google Apps Script, used different epoch (reference date used as 0 for the serialized numbers) and use different time units.

From https://developers.google.com/sheets/api/guides/concepts#datetime_serial_numbers

Date/Time serial numbers

Google Sheets, like most other spreadsheet applications, treats date/time values as decimal values. This lets you perform arithmetic on them in formulas, so you can increment days or weeks, add or subtract two date/times, and perform other similar operations.

Google Sheets uses a form of epoch date that is commonly used in spreadsheets. The whole number portion of the value (left of the decimal) counts the days since December 30th 1899. The fractional portion (right of the decimal) counts the time as a fraction of one day. For example, January 1st 1900 at noon would be 2.5, 2 because it's two days after December 30th, 1899, and .5 because noon is half a day. February 1st 1900 at 3pm would be 33.625.

Note that Google Sheets correctly treats the year 1900 as a common year, not a leap year.

By the other hand, JavaScript use January 1, 1970 00:00 UTC as the epoch date and use milliseconds as the unit.

A word of caution: Custom code to make serialized number conversions from one to the other should consider if the spreadsheet and the Google Apps Script are using the same timezone or not.

That function did exactly what I needed. This function is getting built into a for loop, so I needed the exact 5 digit value of the date to cycle through the selected cells. Thank you very much, I was stumped. — AV Mann, Apr 30 '19 at 00:00

How do I get a string value of the 5 digit serial date format from a cell into my script?

1 Answers1