Check if leap year (February 29) using office script in an excel report

There are 2 Office scripts, one will check in a date cell if the year is a leap year or not (February 29), based on that, it will perform different actions and the other one, it will check the current year without a cell reference.

 

When I use the script ?

When I have to create a calendar.

 

How to create the script ?

Read How to create, edit and select an Office Script in an excel report

 

How to create the button to associate it with the script ?

Read How to create a button and associated it to an Office Script in an excel report

 

How is/are the script(s) ?

Copy the code below and paste it into your script. You will see my comments in green if exist so follow the help to adapt to your need.

To check with a date cell:


function main(workbook: ExcelScript.Workbook) {
    // change sheet1 by yours
    let sheet = workbook.getWorksheet("Sheet1");
    // change A2 by yours
    const dnum = sheet.getRange("A2").getValue() as number;
    const dcvt = new Date((dnum - 25569) * 86400000);
    let dext = dcvt.getFullYear();
    let leapday = new Date(dext, 2, 0).getDate();
    if (leapday == 29) {
        // change B2 by yours
        sheet.getRange("B2").setValue("yes");
    } else {
        sheet.getRange("B2").setValue("no");
    }
}              
              

To check without a cell reference:


function main(workbook: ExcelScript.Workbook) {
  // change sheet1 by yours
  let sheet = workbook.getWorksheet("Sheet1");
  let newyear = new Date().getFullYear();
  let leapday = new Date(newyear, 2, 0).getDate();
  if (leapday == 29) {
    // put your code for leap year
  } else {
    // put your code for normal year
  }
}              
              

If you are looking for the formula version, read Formula: different ways to calculate a calendar with or without bank holiday in an excel report and for the macro version, read Macro: update the year in an excel report.

Interesting Topics