How to Convert from Google Sheet Date Number Value to JavaScript Date

How to convert from Google Sheet date number value to JavaScript date?

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;

Convert Google Sheets date to JavaScript Date

SERIAL_NUMBER outputted by the API is the number of days since December 30, 1899 and the fractional portion (right of the decimal) counts the time as a fraction of the day.

You could verify the start date by using =TO_DATE in your spreadsheet.

Example:

To convert the serial date into a Javascript date, you could follow silkfire's comment here.

References

DateTimeRenderOption

TO_DATE()

Convert Google Sheet date to its number format before importing to Apps Script

This is a pretty simple problem, taking into account that the Date constructor already supports most of the common date formats.

Converting your date to the timestamp is as simple as passing it as an argument to it.

function dateToTimestamp(date) {
  return new Date(date).getTime();
}

dateToTimestamp("Mon Aug 24 06:33:33 GMT+01:00 2020")

It will return a timestamp of 1598247213000

Google sheets get date value as number problem

44202 refers to the number of days passed since the epoch time used in spreadsheets, which refers to December 30th, 1899.

If you want to retrieve the corresponding date in Dart, you can just add this number of days to this epoch date. For example, you could do this:

var epoch = new DateTime(1899,12,30);
var currentDate = epoch.add(new Duration(days: 44202));
print(currentDate); // 2021-01-06

Reference:

• DateTime class

How to convert text to date format in google sheet?

Try this formula in F2:

=ARRAYFORMULA(IFERROR(DATEDIF(
      DATE(
        RIGHT(E2:E,4),
        MATCH(LEFT(E2:E,3),{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"},0),
        MID(E2:E,5,2)), 
      NOW(), "D")))

Update

Revised the formula, which goes in F1 and fills the column, to:

={"Days Left";ARRAYFORMULA(
  IFERROR(-1 * DATEDIF( DATE( RIGHT(E2:E,4), MATCH(LEFT(E2:E,3),{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"},0), MID(E2:E,5,2)), NOW(), "D"),
    IFERROR(DATEDIF( NOW(),DATE( RIGHT(E2:E,4), MATCH(LEFT(E2:E,3),{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"},0), MID(E2:E,5,2)),  "D"))))}

which reverses the date difference values. It also handles date differences for dates either in the future, or in the past.

Convert a date to numeric format google app script

I believe you want to convert the date object or the date string to the serial number using Google Apps Script.

From "I think it's a date object but I'm not sure", I think that 2 patterns can be considered.

Pattern 1:

In this pattern, it supposes that the value of date = active_sheet.getRange("L2").getValue() is the date object. The sample script is as follows.

var active_sheet = SpreadsheetApp.getActiveSheet();
var date = active_sheet.getRange("L2").getValue();
var serialNumber = (new Date(date.getTime() - (1000 * 60 * date.getTimezoneOffset())).getTime() / 1000 / 86400) + 25569; // Reference: https://stackoverflow.com/a/6154953
console.log(serialNumber);

Pattern 2:

In this pattern, it supposes that the value of date = active_sheet.getRange("L2").getValue() is the string value like dd/mm/yyyy. The sample script is as follows.

var active_sheet = SpreadsheetApp.getActiveSheet();
var date = active_sheet.getRange("L2").getValue(); // or getDisplayValue()
var [d, m, y] = date.split("/");
var dateObj = new Date(y, m - 1, d);
var serialNumber = (new Date(dateObj.getTime() - (1000 * 60 * dateObj.getTimezoneOffset())).getTime() / 1000 / 86400) + 25569; // Reference: https://stackoverflow.com/a/6154953
console.log(serialNumber);

Testing:

For both of the above scripts, when a sample value of 21/04/2022 is used, 44672 is returned.

---

Related Topics