Google-sheets – List of dates of current and previous month in Google Sheets

dategoogle sheets

I want to have the dates of the current month and the previous month automatically get populated. So I'm using =MONTH(NOW()) to get the current month and =MONTH(NOW())-1 to get the previous month. Beneath them I'd like the dates to get listed just for that month. So for example it would look like this:

Is this possible?

Best Answer

With the following formula you can construct a month table, containing all days of the month.

Formula

=ARRAYFORMULA(
   DATE(
     YEAR(NOW()),                                                    // first
     A1,                                                             // second
     ROW(                                                            // third
       INDIRECT("A1:A" & DAY(EOMONTH(DATE(YEAR(NOW()), A1, 1), 0)))
     )   
   )
 )

 copy / past
 =ARRAYFORMULA(DATE(YEAR(NOW()),A1,ROW(INDIRECT("A1:A" & DAY(EOMONTH(DATE(YEAR(NOW()), A1, 1), 0))))))

Screenshot

enter image description here

Explained

The DATE formula is used to construct the date out of the three integers it needs. The first is the YEAR, being calculated from the NOW formula. The second argument is the month that you want to calculate the days from. The third argument must be a day number. Because we're using an ARRAYFORMULA, this is fed by a ROW range. The range of the rows, is dependent upon the amount of days per month. This number is calculated as follows:

DAY(                  // days
  EOMONTH(            // days till end of month in date notation
    DATE(
      YEAR(NOW()),    // from year 
      A1,             // from month
      1               // from day    
   )
 )

Example

I've created an example file for you: Days of Month

Related Solutions

Google Sheets – Filter and Subtotal by Month

You don't want to filter the whole column if you need subtotals somewhere below the data. If you filtered the whole column, the subtotal row would be filtered out. So, to filter the data for April:
1. Select your data table
2. Data > Filter
3. Small arrow by the header of the needed column > Filter by condition... > Custom formula is > enter this formula: =month(A2)=4 > then OK
  
  (My dates are in column A)
For subtotals just use the formula below your data like this: =subtotal(9,D2:D7)
In a separate sheet I would use a formula which is not dependent on the filter, like this:
=sum(arrayformula(Sheet1!D2:D7*(month(Sheet1!A2:A7)=4)))
If your sheet name consists of several words use commas when referencing it like this:
'My data'!A2:A7

Google-sheets – Google Script to set the current month once a day only in a cell

Instead of NOW() you could try TODAY(). It's still a volatile function but its result will change only once per day.

If you anyway go for a script, use a time-driver installable trigger.

NOTES:

To learn tha basics about adding a script to a spreadsheet see https://developers.google.com/apps-script/guides/sheets
If you use a bounded script you could use SpreadsheetApp.getActive() to get the spreadsheet that contains the Apps Script project, otherwise you should use SpreadsheetApp.openByid(id) or SpreadsheetApp.openByUrl(url) where id or url are the id / url of your spreadsheet.

Example:

The following is an function to be called by a time-driven trigger that will add the current month number (1-12) to Sheet1!A1.

function respondeToDailyTrigger(){
  var now = new Date(); // Similar to Google Sheets NOW();
  var jsMonth = now.getMonth(); // 0 for January, 1 for February, and so on
  var spreadsheet = SpreadsheetApp.getActive(); // destination spreadsheet
  var sheet = spreadsheet.getSheetByName('Sheet1'); // destination sheet
  var range = sheet.getRange('A1'); // destination cell
  range.setValue(jsMonth + 1);  // add the month number (1-12) to the destination cell
}