In Apps Script, it's easy var timeStamp = new Date().getTime(); var date = new Date(timestamp); But sheets doesn't use JavaScript timestamps for dates, it uses the number of days since 1st Jan 1900, with hours/mins/secs represented as a fraction of a day (an idea copied from Excel - known as DateValue + TimeValue) - so all these are equivalent
The EpochUnix-like systems use 1st Jan 1970 as zero hour (often referred to as the epoch) for dates, so a JavaScript timestamp (13 digits) is the number of milliseconds since then. Prior to then is a negative number. Unix timestamps (10 digit) use the same base, but are the number of seconds rather than milliseconds. Knowing these two base dates makes converting back and forwards with a Sheets (or Excel) formula easy. formula for converting a JS timestamp to a date=yourTimestamp/1000/60/60/24 + date (1970,1,1) formula for converting a date to a JS timestamp=(yourDate-date(1970,1,1))*1000*60*60*24 For more like this, see Google Apps Scripts snippets. Why not join our forum, follow the blog or follow me on twitter to ensure you get updates when they are available.You want to learn Google Apps Script?Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, available All formats are available now from O'Reilly,Amazon and all good bookshops. You can also read a preview on O'Reilly. If you prefer Video style learning I also have two courses available. also published by O'Reilly. |
Services > Desktop Liberation - the definitive resource for Google Apps Script and Microsoft Office automation > Google Apps Scripts snippets >