Making a list of dates from last month-CodePudding

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() } })

}

CodePudding user response：

=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.

CodePudding user response：

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:

CodePudding user response：

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 
  var dates = new Array(len).fill('').map((_, day) => [new Date(year, month-1, day 1)])

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

Dates is a tricky thing always. For example, probably it makes sense to add the timezone to the code, otherwise it can get wrong results in some cases.