0
296
ViewsHow to compare date formats or check if cell is formatted to specific date format
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?
·
Juan Pablo Isaza
2 answers
Answer question0
The most robust way is to use the built-in getNumberFormat() or getNumberFormats() functions
Let's say I have the following 2 cells, with dates formatted differently:
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.
·
Juan Pablo Isaza
Report
0
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");
}
·
Juan Pablo Isaza
Report
Answer question