Date/time handling in JavaScript is a bit of a pain, thankfully there are libraries like Moment JS to save the day.
If you want to integrate better time/date handling from Moment JS in Google sheets (the lazy way), this is how you do it:
- Make a copy of this sheet
- Your formula is simply =momentjs() to get today’s date in YYYY-MM-DD format
How this works:
This lazy function just allows you to add Moment JS classes as a parameter. You’ll need to add the Moment JS library into a script as well.
/**
* Returns a wrapper for the Moment JS Library
*
* @param {".format('YY-MM-DD"} parameters REQUIRED
* @param {"D1"} dummyParam OPTIONAL Include a cell reference to force the custom function to update.
* @return Returns Moment JS functions
* @customfunction
*/
function momentjs(parameters,dummyParam) {
if (!arguments[0]) return eval("moment().format('YYYY-MM-DD')")
return eval("moment()"+(arguments[0]))
}
The dummyParam is a workaround for custom functions not refreshing, which can happen from time to time. To get past this, you would add a cell reference, say D1 and then updating cell D1 would result in the custom function refreshing.
Check out the sheet to see what’s happening: