For dates, Google Sheets gives some number value in roughly pure numerical format like 450345. How does one convert this number into a date value in JavaScript? Tried creating a new Date based off one of these and got a value that does not correspond to the Google Sheets display date value.

For dates, Google Sheets gives some number value in roughly pure numerical format like 450345. How does one convert this number into a date value in JavaScript? Tried creating a new Date based off one of these and got a value that does not correspond to the Google Sheets display date value.

• javascript
• google-apps-script
• google-sheets

• 1 You should be able to get Date object if the value can be converted to a Date object. Can you please show us how you get the value with a screenshot on part of the affected cells? – Thum Choon Tat Commented Nov 1, 2018 at 4:23
• 1 Is this thread useful for your situation? stackoverflow./a/15137090/7108653 – Tanaike Commented Nov 1, 2018 at 4:45
• 1 If the cell is a date and time, then Google Sheets uses a numerical value days since 12/30/1899 0:00:00, where the decimal portion is fraction of a day. Of course if you set the format to date and time you see a calender day and time. Javascript uses milliseconds since 1/1/1970 0:00:00. If you getValue() of a date cell then the conversion to milliseconds is done automatically and it is patible with a javascript date. But if you get the numeric value you have to convert to milliseconds by multiplying by 24*60*60*1000. – TheWizEd Commented Nov 1, 2018 at 15:14

5 Answers 5

Shout-out to TheWizEd for leading me to what looks to be the right answer. Here is a simple conversion script:

function logSheetDateString(GS_date_num, timezone, format) {
    var GS_earliest_date = new Date(1899, 11, 30), 
        //GS_earliest_date gives negative time since it is before 1/1/1970
        GS_date_in_ms = GS_date_num*24*60*60*1000;
    Logger.log(Utilities.formatDate(new Date(GS_date_in_ms + GS_earliest_date.getTime()), 
                                    timezone, format));
}

The conversion to milliseconds since January 1, 1970 may be simplified as:

JS_date_in_ms = GS_date_num * 86400000 - 2209132800000;

These functions convert javascript dates correctly to the inner representation of Google Sheet dates and vice versa.

/**
 * @param {Number} GoogleDateValue - Days passed since dec 30 1899, time in fraction
 * @returns {Date object} - javascript date object
 *
 */
function ValueToDate(GoogleDateValue) {
  return new Date(new Date(1899,11,30+Math.floor(GoogleDateValue),0,0,0,0).getTime()+(GoogleDateValue%1)*86400000) ;
}

/**
 * @param {Date object} - javascript date object{Number} 
 * @returns {Number} GoogleDateValue - Days passed since dec 30 1899, time in fraction
 *
 */

function DateToValue(date) {
  return 25569 + (date.getTime()-date.getTimezoneOffset()*60000)/86400000 ;
}

You can get the date format of the cell and then format it using the Utilities:

var cell = sheet.getRange("C4");
var formattedDate = Utilities.formatDate(cell.getValue(), "GMT", cell.getNumberFormat());
Logger.log(formattedDate);

Remember to change GMT to your timezone.

function job_counter() {


  var yesterday_jobs=0;
  var ss=SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  var e = new Date(new Date().getFullYear(),new Date().getMonth() , new Date().getDate())
  var range_unformated=ss.getRange(2,3,25)
  var range = Utilities.formatDate(range_unformated.getValues(), "GMT", range_unformated.getNumberFormats())
  for (var i=1; i<25; i++){
    if ( range[i] - e <= 0 && range[i] != ""  ){
      yesterday_jobs = yesterday_jobs + 1 
    ss.getRange(16,2).setValue(yesterday_jobs)
    Logger.log(d)
    }}
    // check yesterday_jobs                       
}

I get the message "Cannot find method formatDate(object,string,object)"

Try

let JSdateFormat = new Date(Date.parse(GSheetDateValInNumFormat + 2207520000000));
console.log(JSdateFormat);

2207520000000 is the difference between Google Sheet and Javascript starting time ( |(11:59:59, 30/12/1899) - (00:00:00, 01/01/1970)| ) in milisecond.