I'm trying to format the date in Google App Script and then store it into the Google Sheet. Everything is working fine but the problem is source date has no specific format, dates may e in different formats like DD-MM-YYYY, MM-DD-YYYY, YYYY-MM-DD or date can also e with time and then I've to convert it into YYYY-MM-DD format and then save in google sheet. I've tried to convert to covert using the below codes:

var date = new Date("01-15-2022").toISOString().split('T')[0] // MM/dd/yyyy
Logger.log(date);

this code works only if the source date has in a format like MM/dd/yyyy and yyyy/MM/dd only if the date is in a format like dd/MM/yyyy then codes does not work.

I want a method that converts all the date formats in a single format like yyyy/MM/dd

I'm trying to format the date in Google App Script and then store it into the Google Sheet. Everything is working fine but the problem is source date has no specific format, dates may e in different formats like DD-MM-YYYY, MM-DD-YYYY, YYYY-MM-DD or date can also e with time and then I've to convert it into YYYY-MM-DD format and then save in google sheet. I've tried to convert to covert using the below codes:

var date = new Date("01-15-2022").toISOString().split('T')[0] // MM/dd/yyyy
Logger.log(date);

this code works only if the source date has in a format like MM/dd/yyyy and yyyy/MM/dd only if the date is in a format like dd/MM/yyyy then codes does not work.

I want a method that converts all the date formats in a single format like yyyy/MM/dd

• javascript
• date
• google-apps-script
• google-sheets
• google-apps

Share Improve this question Follow asked Jan 18, 2022 at 8:35 user8560947user8560947 7

• 2 Consider using Intl.DateTimeFormat() instead? – evolutionxbox Commented Jan 18, 2022 at 8:36
• @evolutionxbox, how to use YYYY-MM-DD format inside this function? – user8560947 Commented Jan 18, 2022 at 8:52
• new Date('2022-01-15') works, but new Date('01-15-2022') is an invalid date since Date cannot parse MM-DD-YYYY – evolutionxbox Commented Jan 18, 2022 at 8:54
• 1 Does this answer your question? Format date to MM/dd/yyyy in JavaScript - use Intl for the output, but it should help you parse any date. – evolutionxbox Commented Jan 18, 2022 at 8:58
• 1 "source date has no specific format, dates may e in different formats" then you're stuffed. You can only reliably parse a timestamp if you know the format, or it's unambiguous (e.g. use the month name). Otherwise, you're asking for an unachievable result. – RobG Commented Jan 18, 2022 at 8:59

 |  Show 2 more ments

2 Answers 2

Sorted by: Reset to default 3

I got a little bad news for you. There is no way to distinguish algorithmically between 01-02-2022 (Feb 1, 2022) and 01-02-2022 (Jan 2, 2022). So technically this source data is the infamous 'garbage in'.

Somehow I managed to find an alternative option to achieve this using Moment Js Library below is the solution:

function myFunction() {
  let formats = ["DD/MM/YYYY", "MM/DD/YYYY", "YYYY/MM/DD", "DD/MM/YYYY hh:mm:ss", "DD/MM/YYYY hh:mm:ss A", "MM/DD/YYYY hh:mm:ss A", "YYYY/MM/DD hh:mm:ss A"]
  Logger.log(convertDate("5/1/2022 12:00:00 PM", formats));
}


function convertDate(date, formats){
  eval(UrlFetchApp.fetch('https://cdnjs.cloudflare./ajax/libs/moment.js/2.9.0/moment.min.js').getContentText());
  let dateMomentObject = moment(date, formats);
  let dateObject = dateMomentObject.toDate();
  let dateStr = moment(dateObject).format('YYYY-MM-DD');
  return dateStr;
}