1

So I tried using the simple formula:

=date(2019,3,10) 
=date(2019,3,10)+7
=date(2019,3,10)+14
=date(2019,3,10)+21

... etc.

But when I highlight the first top 3-4 cells to drag down and formulate the pattern it doesn't work in this case. My goal is for my sheet to auto-populate all Sunday's date in the year, even continuing to the next couple years in the future.

Any recommendations?

  • Try selecting cells 2-4 (don't select the first cell) then drag down (the first formula doesn't has the same pattern as the others) – Rubén Mar 13 at 14:01
0
  • instead of =DATE(2019,3,10)+7 use this formula and drag it down

    =DATE(2019,3,10)+ROW(A1)*7

    0

  • 1
    Damn you guys are good. Thank you for this! – timmyturner Mar 13 at 15:05
  • 1
    Btw, this formula was the most simplified and straight to the point. Even Arrayformula is new to me but thats a new topic I can learn now as an alternative. Thanks. – timmyturner Mar 13 at 16:55
0

Welcome! I hope you get it working the way you'd like. Have you considered an ARRAYFORMULA?

=ARRAYFORMULA(DATE(2019,3,10)+(ROW(1:10)-1)*7)

Try pasting that into a cell with 10 blank cells under it. It only goes to 10 dates for this example, but it could be whatever length you choose. Array formulas are great because they do your requested calculations and operations on a whole incoming set of cells or values, rather than just for the single cell they occupy. I'm typing this out in painful detail for clarity, but nobody reading this should be intimidated—they're easy to use once one becomes familiar with them.

=ARRAYFORMULA( DATE(2019,3,10) + ( ROW(1:10) -1 ) *7 )

In english, in order of how it's calculated:
=ARRAYFORMULA()
means to do the contained formula over and over to cover all the incoming data.
DATE(2019,3,10)
as you know, DATE just generates a date in the format Sheets then understands.

+(ROW(1:10)-1)*7
Your example contained an increasing list of 7's multiples. That's the final thing we need here too; your 7, 14, 21… "addends" list. But we don't want to have to type them out, or even drag them down, so here I'm using a common trick and asking Sheets' ROW function to output "the row number of the first ten rows". ROW(1:10)

No surprises—the row numbers of the first ten rows are just the numbers 1 to 10. Since that's a whole array of numbers though, the ARRAYFORMULA will do the math once for each integer 1 through 10.

One detail needs addressing; we can think of your list as actually starting by adding 0 days to the first day—we don't want to add a week to the first date. So here we don't want 1 to 10; we want to add 0 to 9. So for each repetition, it -1 subtracts one from the current number.

Finally, we multiply that times seven *7, so our list 0, 1, 2… becomes 0, 7, 14…
That list, 0 through 63 is then added to the DATE you chose, with one output cell for each input number. So to generate, say 52 weeks worth, you'd just change it to ROW(1:52).

It all 'lives' in one cell, you can change it in just that one spot in the future, it's flexible, it's automatic, and it's very quick even for huge output sets. I hope I haven't scared you or anyone else off—ARRAYFORMULAs are an amazing tool.

  • 1
    Damn you guys are good. I appreciate the clarity of the breakdown! Was very insightful – timmyturner Mar 13 at 15:06
0
  • also you can use ARRAYFORMULA and list every Sunday from 2019-3-10 to 2020-3-10

=ARRAYFORMULA(TO_DATE(FILTER(ROW(INDIRECT("A"&
 DATEVALUE("2019-3-10")&":A"&
 DATEVALUE("2020-3-10"))), MOD(ROW(INDIRECT("A1:A"&COUNTA(ROW(INDIRECT("A"&
 DATEVALUE("2019-3-10")&":A"&
 DATEVALUE("2020-3-10"))))))-1, 7)=0)))

0

  • 1
    Damn you guys are good. Thank you for this! I now must learn about array formulas – timmyturner Mar 13 at 15:06
  • @timmyturner pls accept the answer which solved your problem and upvote all answers which were helpful to you – user0 Mar 13 at 15:10
0
  1. In the first cell put in the date you want.

    For example in cell A23 enter 3/10/2019

  2. In the cell right below it put the formula:

    =A23+7

  3. copy this cell down as far as you need.

That's it.

  • This was my initial try and it didn't work out as intended – timmyturner Mar 13 at 19:33
  • The example in your question is more complex than it needs to be. it is just "=then-click-on-cell-above+7". You don't have to include the original date. – mhoran_psprep Mar 13 at 19:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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