Tuesday, July 16, 2013

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:
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,

2 comments:

  1. That did it, Kishan! Thank you!

    After I posted I came up with something that worked but not nearly as elegant as yours:

    = text(EDATE(DATEVALUE( right(A2;4)&"-"&SPLIT(A2, "/")&"-1"),1),"m/yyyy")

    And with yours I can define the range by editing the row (A1:A15) and not having to copy and paste the formula into the other cells.

    Thanks again!

    ReplyDelete
  2. Any way this could be modified to show the dates of a specific month. So if December was at the top it would list 12/01/2013, 12/02/2013, etc.

    Thanks for any help, really can't figure out how to do this!

    Tim

    ReplyDelete