How to compare date formats or check if cell is formatted to specific date format-CodePudding

I need a way to check if a cell is already formatted to a specific date format. If TRUE do something, if FALSE do something else.

Something like:

var myDateFormat = "dd"/"mm"/"yyyy" "hh":"mm":"ss"

if (sheet.getRange("A1") === myDateFormat)  {
console.log("TRUE");
}else{
console.log("FALSE");
}

The above does not work. What can be changed?

CodePudding user response：

You can use Javascript regex to validate the format of the date string.

var pattern = /^([1-9]|([012][0-9])|(3[01]))-([0]{0,1}[1-9]|1[012])-\d\d\d\d [012]{0,1}[0-9]:[0-6][0-9]:[0-6][0-9]$/g;

if (pattern.test(dateString){
    console.log("TRUE");
}
else{
    console.log("FALSE");
}

CodePudding user response：

The most robust way is to

I can now write something like this and launch main():

const getDateFormats = range =>
  SpreadsheetApp.getActiveSpreadsheet()
    .getActiveSheet()
    .getRange(range)
    .getNumberFormats()
    .flat();

const main = () => {
  const formatsInRange = getDateFormats('a1:a2')
  console.log(formatsInRange);
  formatsInRange.forEach((format, i) =>
    /^dd\/MM\/yyyy$/.test(format)
      ? console.log(`Value ${i   1} is OK`)
      : console.log(`Value ${i   1} is not OK`)
  )
};

This script produces the following output:

The formats that are returned by this function are referenced here. And you can always console.log() the needed format first so that you can use it as a regular expression later.

Let me know if you have any questions.