This script is to get the previous, current and next dates. The result will be in European version (DD/MM/YYYY) but I also show you to display it in US version (MM/DD/YYYY). With this script, you can customize it to get only the day, the month or the year instead to show the full date.

 

When I use the script ?

Generally, I use this script to get the month in its short or full name.

 

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.

function main(workbook: ExcelScript.Workbook) {
    // change sheet1 by yours
    let sheet = workbook.getWorksheet("Sheet1");
    let currentDate = new Date();
    let previousDate = new Date();
    // to get only month, change setDate by setMonth and getDate by getMonth
    // to get only year, change setDate by setFullYear and getDate by getFullYear
    previousDate.setDate(previousDate.getDate() - 1);
    let nextDate = new Date();
    nextDate.setDate(nextDate.getDate() + 1);
    // to format it as dd/mm/yy, put 2-digit
    // to get only day, keep only day: '2-digit'
    // to get only month, keep only month: 'long'
    // to get only year, keep only year: 'numeric'
    let options: Intl.DateTimeFormatOptions = { year: 'numeric', month: 'long', day: '2-digit' };
    // put US instead of UK for US format
    let resultprevious = previousDate.toLocaleDateString('en-UK', options);
    let resultcurrent = currentDate.toLocaleDateString('en-UK', options);
    let resultnext = nextDate.toLocaleDateString('en-UK', options);
    // virtual results
    console.log("Previous Date: " + resultprevious);
    console.log("Current Date: " + resultcurrent);
    console.log("Next Date: " + resultnext);
    // results in cells
    sheet.getRange("B2").setValue(resultprevious);
    sheet.getRange("B3").setValue(resultcurrent);
    sheet.getRange("B4").setValue(resultnext);
}