0

I'd like to create a Google Apps Script that changes the format of all dates in a sheet based on the year.

  • If the date is in the current year, the format should be, e.g., Aug 5
  • If it's in an earlier or later year, the format should be, e.g., 3/5/2025

Here's a few details:

  • I want to make sure I'm not changing any data (e.g., by using Utilities.formatDate), instead just formatting the cells with dates in them (from what I've read, setNumberFormat is the way to do that).
  • I read that a reliable way to check if a cell contains a date and not a number is with if (cell instanceof Date){}
  • I'm working with sheets that span a few hundred rows across about 20 columns, so the more efficient the script is, the better.

My coding skill level is nearly 0, but I've searched plenty and found very little on formatting dates. I've also tried using Chat GPT to generate this many times and never got results that worked.

2
  • This question is too broad. I suggest you try to write or adapt a script and then post any specific questions you have.
    – Blindspots
    Mar 8 at 15:00
  • @Blindspots I don't know how to write scripts, nor could I find a starting point that seemed suitable to adapt after much searching. I tried to be as specific as I could, but if there's a way I could be more specific without knowing how to code, please let me know.
    – gills
    Mar 9 at 3:03

2 Answers 2

2

The following code writes all formats in a sheet in one go which is faster than writing values one by one. It skips hidden sheets and detects protected sheets where the account that runs the script doesn't have full edit rights.

You can choose which sheets and which ranges to format by editing the sheetsToFormat regex and the rangeA1ToFormat constant.

To learn how to configure formats, see Date & number formats.

/** @OnlyCurrentDoc */
'use strict';

/**
* Formats dates and date times differently depending on whether
* the date is in the current year or in another year.
* Does not modify formats of times, durations, numbers or text.
* Does not modify hidden sheets.
*/
function formatDatesOnManySheets() {
  // version 1.0, written by --Hyde, 9 March 2024
  //  - see https://webapps.stackexchange.com/q/175116/269219
  const sheetsToFormat = /(.+)/i; // use /(Sheet1|Sheet2|Sheet3)/i to format those three tabs; use /(.+)/i to format all tabs
  const rangeA1ToFormat = ''; // use 'A2:F' to format the first six columns; leave empty to format dates in the whole sheet
  const currentYearFormat = 'MMM d';
  const otherYearFormat = 'M/d/yyyy';
  let numSheetsFormatted = 0;
  let numCellsFormatted = 0;
  let sheetName;
  const ss = SpreadsheetApp.getActive();
  try {
    ss.toast('Formatting dates across the spreadsheet...', '', 30);
    const currentYear = new Date().getFullYear();
    ss.getSheets().forEach(sheet => {
      if (sheet.isSheetHidden()) return;
      sheetName = sheet.getName();
      if (!sheetName.match(sheetsToFormat)) return;
      numSheetsFormatted += 1;
      const range = rangeA1ToFormat ? sheet.getRange(rangeA1ToFormat) : sheet.getDataRange();
      const dates = range.getValues();
      const newFormats = range.getNumberFormats().map((row, rowIndex) => row.map((format, columnIndex) => {
        const date = dates[rowIndex][columnIndex];
        if ('[object Date]' !== Object.prototype.toString.call(date)) return format;
        const year = date.getFullYear();
        if (1896 < year && year < 1904) return format; // the date object represents a time or duration
        numCellsFormatted += 1;
        return year === currentYear ? currentYearFormat : otherYearFormat;
      }));
      range.setNumberFormats(newFormats);
      SpreadsheetApp.flush();
    });
    ss.toast(`Done. Formatted ${numCellsFormatted} cells in ${numSheetsFormatted} sheets.`, '', 8);
  } catch (error) {
    ss.toast(`Could not finish. Is sheet '${sheetName}' protected?`, '', 30);
    throw error;
  }
}

When 2025 comes, the format of dates in 2024 will stick and remain like Mar 9. You can run the code again then to reformat the dates for the new year.

4
  • This is obviously exceptional code, and it works absolutely perfectly. Can you tell me what part of this is making it work so efficiently / in one move, compared to the other answer's code that updates each cell one at a time?
    – gills
    Mar 11 at 20:01
  • 1
    Range.setNumberFormats(). Mar 11 at 21:39
  • Perfect. I want to change the sheetsToFormat constant to the active spreadsheet so I can use this from the Macros menu. I tried const sheetsToFormat = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); but that doesn't seem to be working. What am I doing wrong?
    – gills
    Mar 12 at 23:02
  • You are replacing a regex with a Sheet object, which does not work. Replace ss.getSheets() with [SpreadsheetApp.getActiveSheet()] instead. Ask only one question per post. Post a new question if necessary. Mar 12 at 23:58
0

This example applies to a single column of data. I did limited testing mixing in some different numbers (11, 9, 2004, etc.) that weren't being formatted as dates initially. The script is commented up and I left in the logger pieces so you can see what's happening with different cell data.

function dateFormatter() {
  const currentYear = new Date().getFullYear();//gets this year
  const ss = SpreadsheetApp.getActiveSpreadsheet();//gets this workbook
  const sheet = ss.getSheets()[0];//gets the first sheet
  const range = sheet.getRange("a1:a10").getValues();//get a range, in this case a1 through a10
  range.forEach(function(cell,index) {//loop it
  Logger.log(isNaN(parseFloat(cell[0])));//Crappy way to check if it's a number or a date
 if(isNaN(parseFloat(cell[0]))){
  const cellYear = new Date(cell).getFullYear();      
  const rowNum = Math.trunc(index+1);
    if(currentYear === cellYear){//if year matches current year
          const value = sheet.getRange(rowNum, 1).getValue();
          Logger.log('we match-'+rowNum + ' ' + value)
          sheet.getRange(rowNum, 1).setNumberFormat('mmm d');//set format
        } else {
          Logger.log('no match-'+rowNum);//no match
          sheet.getRange(rowNum, 1).setNumberFormat('d/m/yyy');//set other format
        }
    
      } 
 
  })
}
2
  • This works! It runs slightly slowly, updating cells one at a time. Is this just the nature of how Google Apps Scripts like this work, or is there a way to make it more efficient/quicker? If it there's a way to make them update all in one go, it would also make a single undo command instead of hundreds.
    – gills
    Mar 9 at 2:54
  • Also, this made me wonder if there's a "proper" way to check if a cell contains a date, so I searched more and found this code that checks for a date object: if (cell instanceof Date) I couldn't get it to work in the script for some reason though.
    – gills
    Mar 9 at 2:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.