Forum Discussion

ianwuk's avatar
ianwuk
Iron Contributor
Nov 20, 2018
Solved

How to automatically fill rows with dates based on cell value?

Hi Everyone.   I have a spreadsheet that looks like this:     How can I make it for whatever number I put in B2 it takes the date in A4 and extends it by the number of months from B2 and a...
excel
Formulas and Functions
Macros and VBA
  • Haytham Amairah's avatar
    Haytham Amairah
    Nov 20, 2018

    Hi,

     

    I think the most robust way to do this is as follows:

    • Link cell A4 to the cell B2 by just using this formula in cell A4 =B2
    • Then use this formula in the cell A5 =IF(ROW(A1)>=$B$1,"",EDATE($A$4,ROW(A1)))
    • And then drag it down to whatever limit you want

     

     

    Please find this in the attached file.

    Hope that helps

Haytham Amairah's avatar
Haytham Amairah
Silver Contributor
Nov 20, 2018

Hi,

 

I think the most robust way to do this is as follows:

  • Link cell A4 to the cell B2 by just using this formula in cell A4 =B2
  • Then use this formula in the cell A5 =IF(ROW(A1)>=$B$1,"",EDATE($A$4,ROW(A1)))
  • And then drag it down to whatever limit you want

 

 

Please find this in the attached file.

Hope that helps

ianwuk's avatar
ianwuk
Iron Contributor
Nov 20, 2018
Thanks for replying. Is there any way for the new date cells to be added automatically when I put a number in to cell B2?

Thanks.
  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    Nov 20, 2018

    Hi,

     

    As the screenshot you attached, the number of months is in the cell B1 not in cell B2!

    What is in cell B2 is the start date!

     

    So the dates should automatically be filled in cells when you change the number in cell B1.

    Please see the attached file in my previous reply to test that!

     

    Regards

    • ianwuk's avatar
      ianwuk
      Iron Contributor
      Nov 20, 2018

      Hi.

       

      My mistake, I meant B2, not B1.

       

      Thank you for the attached file too.  I will check it out.

      • ianwuk's avatar
        ianwuk
        Iron Contributor
        Nov 21, 2018

        Is it also possible to use your formula and modify it so that depending on the number I put in B1 it then, in cell B4, counts up to that number?  I want to use it as repayment numbers.

         

        So, it would look like this if I put 6 in B5 :

         

         

        Thanks for all the help.

Resources