Convert date to google spreadsheet in JS Date object?

Question

I spun around on this ... I have a spreadsheet that contains two dates, and I need to find the number of years that have passed between them (that is, someone age for a given date is a replacement for Excel DATEDIF).

The first step is to convert the Google serial number to a JS Date object, but it doesn't seem to be the Date constructor that does this. Any ideas?

Thanks.

Answer 1

I know that you are happy with your decision in its current form, but I just wanted to add my comments on how Google Apps Script deals with "dates", either passed in a user-defined function or retrieved from a cell using getValue ().

My rule of thumb is that if the worksheets (spreadsheet application) provide a value formatted as a date (either by automatic coercion or by a user specifying a format), then Google Apps Script automatically holds that value as a date object.

For example:

function returnDate(value) { return new Date(value); } 

If you enter 1/1/13 into A1, and in another cell you call =returnDate(A1) , it will return the same date (as if you just had return value; in the code). However, see what happens when you format A1 as "Normal" (convert it to a numeric value). Here, the "Serial Number of the Sheet" (number of days from 30/12/1899) is converted to a date object using Google Apps Script, but in GAS it is "considered" as the number of milliseconds since midnight 1/1/1970. This way, you can get unexpected results if you pass in numerical values ​​that you think are representative of the date.

Also compare:

=returnDate(DATE(2013;1;1))

=returnDate(VALUE("1/1/13"))

=returnDate(DATEVALUE("1/1/13"))

=returnDate("1/1/13")

=returnDate("1/1/2013")

The last two “work” because new Date() successfully creates a date object from a valid string, but note that Sheets is automatically snapped to the current century, and GAS provides a two-digit year to the 1900s.

So IMO, if you want it to behave the same way as in Excel (that is, "treat" the numeric value as the serial number for the date), you will need to first check if the passed parameter is a date object (or "valid "text string), and if not, mathematically convert it from" days from 30/12/1899 "to" milliseconds from 1/1/1970 ", and then new Date() .

Apologies for the long post.

Answer 2

To convert Google spreadsheet date to javascript date:

 var JSdate = Date.parse(Cell.getValue()) 

To convert javascript date to google spreadsheet date:

 function GoogleDate( JSdate ) { var D = new Date(JSdate) ; var Null = new Date(Date.UTC(1899,11,30,0,0,0,0)) ; // the starting value for Google return ((D.getTime() - Null.getTime())/60000 - D.getTimezoneOffset()) / 1440 ; } 

Answer 3

This is what I did:

 function numberToDate(number){ var date = new Date(number * 24 * 60 * 60 * 1000); date.setFullYear(date.getFullYear() - 70); date.setDate(date.getDate() - 1); return (date); } 

It may seem a little dirty, but this is the only solution I have found now

Answer 4

After several experiments, it turned out that it just works, which was a surprise. new Date(cell) seems to internally convert the serial number to a string sufficient to create a date object. Full answer:

 function datedif(first, second, format) { var e1 = new Date(first); var e2 = new Date(second); var age = e2.getFullYear() - e1.getFullYear(); if( (e2.getMonth() < e1.getMonth()) || ((e2.getMonth() == e1.getMonth()) && (e2.getDate() < e1.getDate()))) age--; return age; } 

Answer 5

Under "serial number," I assume that you are talking about unix time in seconds or milliseconds from an era. You can simply use the standard Date Javascript object:

 new Date(value); 

Google is your friend from there. Here are some links to get started:

• Link to W3 Schools
• W3 Schools Tutorial
• MDN Link

Javascript allows you to do simple subtractions with two dates, returning you the time difference in ms.

 var timeDiffInMS = date2 - date1; 

That should be all you need to understand this, so I will leave the calculations of years as an exercise for the reader.

Answer 6

You can try the following:

 return new Date(1900, 0, --excelDate)