0

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:

1

Is this possible?

2
  • Do you want the dates to be automatically generated? Dec 15, 2013 at 20:57
  • yes, automatically generated. So they get updated as the date/month changes.
    – user54941
    Jan 1, 2014 at 20:53

2 Answers 2

1

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

1
0
  • cell A1:

    ={MONTH(TODAY()); ARRAYFORMULA(TO_DATE(ROW(
      INDIRECT("A"&DATE(YEAR(TODAY()), MONTH(TODAY()), 1)):
      INDIRECT("B"&EOMONTH(TODAY(), 0)))))}

  • cell B1:

    ={MONTH(TODAY())-1; ARRAYFORMULA(TO_DATE(ROW(
      INDIRECT("A"&DATE(YEAR(TODAY()), MONTH(TODAY())-1, 1)):
      INDIRECT("B"&EOMONTH(DATE(YEAR(TODAY()), MONTH(TODAY())-1, 1), 0)))))}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.