I am trying to get the date format currently displaying as Monday 25 @ 7:30 PM but I would like it to display showing the relevant suffix i.e. 'th' or 'st' or 'nd' i.e. 4th, 1st, 2nd etc.
I am using the code below but getting the error message "Exception: Invalid argument: date. Should be of type: Date" any ideas? Much apprecitated
function getOrdinal(date) {
var d = new Date(),
suffix = ['th', 'st', 'nd', 'rd'][(d > 3 && d < 21) || d % 10 > 3 ? 0 : d % 10];
return Utilities.formatDate(date, Session.getScriptTimeZone(), "d'" + suffix + "MMMM, yyyy");
}
Your goal is to convert the date in the sheet to Monday 25th January 2021.
You can use your current solution to obtain 25th and then use Utilities.formatDate to obtain day, month and year.
Template literals were used to concatenate the resulting string.
Execute myFunction to paste the new format in column M assuming the data input is in column L (see screenshot):
function myFunction(){
const ss = SpreadsheetApp.getActive();
const sh = ss.getSheetByName('Sheet1'); // put the name of your sheet
const dts = sh.getRange('L1:L6').getValues().flat();
const new_dts = dts.map(c=>[getOrdinal(c)]);
sh.getRange(1,13,new_dts.length,1).setValues(new_dts);
}
function getOrdinal(input) {
const dt = new Date(input);
const d = dt.getDate();
const suffix = ['th', 'st', 'nd', 'rd'][(d > 3 && d < 21) || d % 10 > 3 ? 0 : d % 10];
const dayN = Utilities.formatDate(dt, Session.getScriptTimeZone(), "EEEE");
const ds = d+suffix;
const rd = Utilities.formatDate(dt, Session.getScriptTimeZone(), "MMMM yyyy");
return `${dayN} ${ds} ${rd}`;
}