Caper123

Q: Numbers:  trying to auto fill dates for every three weeks

Numbers:  Trying to Auto Fill dates for every three weeks?   Unable to do so...any suggestions...thanks..

MacBook Air (13-inch, Early 2015), OS X El Capitan (10.11.6)

Posted on Sep 25, 2016 7:02 AM

Close

Q: Numbers:  trying to auto fill dates for every three weeks

  • All replies
  • Helpful answers

  • by Wayne Contello,

    Wayne Contello Wayne Contello Sep 25, 2016 8:56 AM in response to Caper123
    Level 6 (19,416 points)
    iWork
    Sep 25, 2016 8:56 AM in response to Caper123

    Here is my guess on what you are asking for:

     

    Screen Shot 2016-09-25 at 10.54.53 AM.png

     

    make sure the table has 22 rows as shown.

     

    enter the starting date in cell E1

    A2=1+INT((ROW()−2)÷7)

     

    this is shorthand for... select cell A2, then type (or copy and paste from here) the formula:

    =1+INT((ROW()−2)÷7)

     

    B2=MOD(ROW()−2, 7)+1

    C2=E1+ROW()−2

     

    select cells A2 thru C2, copy

    select cell A2 thru the end of column C, paste

  • by Barry,

    Barry Barry Sep 25, 2016 4:24 PM in response to Caper123
    Level 7 (32,714 points)
    iWork
    Sep 25, 2016 4:24 PM in response to Caper123

    Hi Caper,

     

    My reading of the question is different from Wayne's. Is this what you are trying to do?:

    Screen Shot 2016-09-25 at 4.13.30 PM.png

    Each date in column C is three weeks after the date in the previous row. There are several ways of doing this:

     

    1. Enter the first date in C2, and the second in C3. Select both cells, then grab the small yellow circle that shows in the middle of the bottom line of the blue selection rectangle when you bring the mouse near there, and drag down for as many rows as you need. (The "Start date" in C1 is optional for this method. C1, in a Header row) can have any content.)

    2. Use a formula. The list above was made using a formula that added a multiple of 21 to the "Start date" in C1. The multiple was determined by the Row in which the copy of the formula was located. In Row 2, the multiple was zero, producing the same date as the Start. The formula in C2 is below.

      C2: =C$1+(ROW()−2)×21

      Fill down as far as needed.

     

    Regards,

    Barry

  • by Caper123,Solvedanswer

    Caper123 Caper123 Sep 25, 2016 4:39 PM in response to Caper123
    Level 1 (8 points)
    iWork
    Sep 25, 2016 4:39 PM in response to Caper123

    Thank you to both Wayne and Barry.  Wayne I could not get it to work with your format; however, sincere thanks for assisting me.    Barry your way worked perfectly for me.  I had spent hours trying to get this to work.  I even called the help line to no avail.  So I really appreciate the help.  Best regards, Caper123