google-sheetsgoogle-apps-scriptcustom-function

Trying to get date format in Google Sheets to display as 1st, 2nd, 3rd etc with the relevant suffix using google scripts editor


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");
}

enter image description here


Solution

  • Explanation:

    Your goal is to convert the date in the sheet to Monday 25th January 2021.

    Code snippet:

    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}`;
    }
    

    Example sheet of code snippet:

    enter image description here