Google Spreadsheet Auto Populate dates by adding a month in above cell

Question:

( by jackRoarkkk )

Hi, 

In a spreadsheet, how do I add 1 month to a date, then display it in this format?: m/yyyy

Here's what I tried:

A1: 7/15/2013

A2: =text(DATE( YEAR(A1) ; MONTH(A1)+1 ; DAY(A1) ),"m/yyyy")

A3: =text(DATE( YEAR(A2) ; MONTH(A2)+1 ; DAY(A2) ),"m/yyyy")

A4: =text(DATE( YEAR(A3) ; MONTH(A3)+1 ; DAY(A3) ),"m/yyyy")

etc....

A2 displays 8/2013, like I want, but A3, A4, etc. break, because the previous cell is no longer a date. I need something that works like this fake formula would:

A1: 7/15/2013

A2: DateFormat(A1+ 1 month,"m/yyyy") displays 8/2013

A3: DateFormat(A2+ 1 month,"m/yyyy") displays 9/2013

A4: DateFormat(A3+ 1 month,"m/yyyy") displays 10/2013

etc...

Thanks!

Solution:

Have a look at the following screenshot of my spreadsheet:

I have the following formula in Cell A2:

=ArrayFormula( text( date( year(A1) ; month(A1) + row(A1:A15) ; day(A1) ),"m/yyyy") )

the above formula will automatically populate up to Cell 16 if you want more or less then edit the row(A1:A15) in the above formula as per your requirement.


And if you don't want to auto populate then you can put the following formula in Cell A2:
=ArrayFormula( text( date( year($A$1) ; month($A$1) + 1 ; day($A$1) ),"m/yyyy") )

And the following formula in Cell A3:
=ArrayFormula( text( date( year($A$1) ; month($A$1) + 2 ; day($A$1) ),"m/yyyy") )

and so on...


I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive