Making a list of dates from last month

Question

I want to make a list of last months dates in cell A5:A35.

I am currently just using formulas but it is going to list all the days regardless of count in the month. So when I make the sheet for November, it's going to have December 1st on the list. I don't want that.

I tried scripting to get the current month and searching the range for that month but it's not working and seems convoluted. There must be a cleaner way.

I just want to programmatically list the days in the prior month.

I have this

Code function won't work on mobile

function assignDates() {
const cell = sheet.getRange('A5:A35'); 
cell.setFormula(=EOMONTH(TODAY(),-2)+D5

d5 is a hidden column with 1,2,3... etc. It's a really cheap way to do it.

It will list 31 days regardless of the length of the month.

Now to deal with this, I tried to make a script to get the current month and then delete entries that contain that but it does not work.

//check to see if dates fall within month
function dateCheck(sheet){
var sheet = SpreadsheetApp.getActive().getSheetByName('test');
//get current month 
var month = Utilities.formatDate(new Date(), "GMT-5", "MMMM")
// Delete days that fall out of range
    var dayRange = sheet.getRange('A5:A36').getDisplayValues();
    dayRange.forEach((date) => { if (date.toString().includes(month)) 
{ sheet.getRangeList(dayRange).clearContent() } })

}

Answer 1

If you need a script here you go:

function myFunction() {
  var today = new Date();
  var year  = today.getFullYear();
  var month = today.getMonth(); // 0 -> January, 1 -> February, etc...

  // get the number of days of the previous month
  // 'zero day' of a month is the last day of a previous month
  var len = new Date(year, month, 0).getDate();

  // make an array with dates (strings, actually)
  var options = { year: 'numeric', month: 'numeric', day: 'numeric' };
  var dates = new Array(len).fill('').map((_, day) =>
    [new Date(year, month-1, day+1).toLocaleString('en-US', options)])

  // put the array on the sheet
  SpreadsheetApp.getActiveSheet().getRange(5,1,len,1).setValues(dates);
}

Dates is a tricky thing always.

Answer 2

Nice challenge!

Enter the following function on A5:

=arrayformula(if(eomonth(today(),-2)+row(A5:A35)+1-row()>eomonth(today(),-1),"",eomonth(today(),-2)+row(A5:A35)+1-row()))

It will retrieve all dates from the 1st until the last date of the last month (related to today()). until 31th, 30th, 28th, or 29th dynamically!

Cheers!

Answer 3

Get Last Month and This Month Calendar on a Spreadsheet

Code:

function getCalendar() {
  const ss = SpreadsheetApp.getActive()
  const sh = ss.getActiveSheet();
  sh.clear();

  let oA = [];
  oA.push(monthlyCalendar(new Date().getMonth() - 1, null, true));//helper function
  oA.push(monthlyCalendar(new Date().getMonth(), null, true));//helper function
  //oA.push(monthlyCalendar(new Date().getMonth() + 1, null, true));
  oA.forEach((obj, i) => {
    if (i == 0) {
      sh.getRange(1, 1, 2, obj.cA[0].length).setFontWeight('bold');
      sh.getRange(1, 1, obj.cA.length, obj.cA[0].length).setValues(obj.cA);
    } else {
      let sr = sh.getLastRow() + 2;
      sh.getRange(sr, 1, 2, obj.cA[0].length).setFontWeight('bold');
      sh.getRange(sr, 1, obj.cA.length, obj.cA[0].length).setValues(obj.cA);
      if (obj.roff && obj.coff) {
        sh.getRange(sr, 1).offset(obj.roff, obj.coff).setFontWeight('bold').setFontColor('red');//sets the current date to bold and red
      }
    }
  });
}

Helper Function:

function monthlyCalendar(m, wsd, ret) {
  var m = m || new Date().getMonth();
  var wsd = wsd || 1;//defaults to Monday
  var ret = ret || false;
  const td = new Date();
  const [cy,cm,cd] = [td.getFullYear(),td.getMonth(),td.getDate()];
  const dA = ['Sun', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat'];
  const oA = [...Array.from(Array(7).keys(), idx => dA[(idx + wsd) % 7])]
  let dObj = {};
  let midx = {};
  let rObj = {cA:null,roff:null,coff:null};
  oA.forEach(function (e, i) { dObj[e] = i; });
  const mA = [...Array.from(new Array(12).keys(), x => Utilities.formatDate(new Date(2021, x, 15), Session.getScriptTimeZone(), "MMM"))];
  mA.forEach((e, i) => { midx[i] = i; })
  let cA = [];
  let bA = [];
  let wA = [null, null, null, null, null, null, null];
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  sh.clear();
  const year = new Date().getFullYear();
  let i = midx[m % 12];
  let month = new Date(year, i, 1).getMonth();
  let dates = new Date(year, i + 1, 0).getDate();
  cA.push([mA[month], dates, '', '', '', '', '']);
  bA.push(['#ffffff', '#ffffff', '#ffffff', '#ffffff', '#ffffff', '#ffffff', '#ffffff']);
  cA.push(oA)
  //bA.push(['#ffff00', '#ffff00', '#ffff00', '#ffff00', '#ffff00', '#ffff00', '#ffff00']);
  let d = [];
  let ddd = [];
  for (let j = 0; j < dates; j++) {
    let day = new Date(year, i, j + 1).getDay();
    let date = new Date(year, i, j + 1).getDate();
    if (day < wA.length) {
      wA[dObj[dA[day]]] = date;
    }
    if(cy == year && cm == month && cd == date) {
      rObj.roff = cA.length;
      rObj.coff = dObj[dA[day]];
    }
    if (dA[day] == oA[wA.length - 1] || date == dates) {
      cA.push(wA);
      //bA.push(['#ffffff', '#ffffff', '#ffffff', '#ffffff', '#ffffff', '#ffffff', '#ffffff']);
      wA = ['', '', '', '', '', '', ''];
    }
  }
  if (!ret) {
    rObj.cA = cA;
    sh.getRange(1, 1, rObj.cA.length, rObj.cA[0].length).setValues(cA);
    if (rObj.roff && rObj.coff) {
        sh.getRange(1, 1).offset(rObj.roff, rObj.coff).setFontWeight('bold').setFontColor('red');
      }
  } else {
    rObj.cA = cA;
    return rObj;
  }
}

Demo:

Answer 4

=SEQUENCE(DAYS(EOMONTH(TODAY(),)+1,EOMONTH(TODAY(),-1)+1),1,EOMONTH(TODAY(),-1)+1)

• DAYS to calculate number of days in this month
• EOMONTH to get end date of last month and this month
• SEQUENCE to create sequence of dates.

You need to change TODAY() to a static date string, if you don't want the sequence to change every month.