Patrick
Patrick

Reputation: 85

Making a list of dates from last month

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

}

Upvotes: 1

Views: 1126

Answers (4)

Yuri Khristich
Yuri Khristich

Reputation: 14537

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.

Upvotes: 1

AJ Suarez
AJ Suarez

Reputation: 43

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!

enter image description here

Cheers!

Upvotes: 1

Cooper
Cooper

Reputation: 64140

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:

enter image description here

Upvotes: 1

TheMaster
TheMaster

Reputation: 50914

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

Upvotes: 1

Related Questions