Google spreadsheets serial date

Question

I am using Google Apps Script editor and am trying to convert a Google spreadsheet serial date to a date formatted mm/dd/yyyy. The serial date is 42394 right now. Is there a way to convert it within my code on Google Apps Script?

Answer 1

Based from the documentation, use formatDate(date, timeZone, format) method. It formats date according to specification described in Java SE SimpleDateFormat class.

Use this code to change the date notation:

var formattedDate = Utilities.formatDate(new Date(), "GMT", "mm/dd/yyyy");

Hope this helps!

Answer 2

Count of days

If the number 42,394 was meant to be in year 2016 (2016-01-27), then apparently it represents the number of whole days since 1900.

Google Apps Script seems to be a variant of JavaScript/ECMAScript 5 which I do not know. But I can show you the idea in Java, starting with the first of 1900 and adding the number of days.

java.time.LocalDate

In Java 8 and later, the LocalDate class represents a date-only value without time-of-day and without time zone.

LocalDate epoch = LocalDate.of ( 1900 , Month.JANUARY , 1 );
int input = 42_394;
LocalDate localDate = epoch.plusDays ( input );

Dump to console.

System.out.println ( "input: " + input + " | from epoch: " + epoch + " = localDate: " + localDate );

input: 42394 | from epoch: 1900-01-01 = localDate: 2016-01-27