• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula for automatically Month

Ramesh_vm

Ramesh_vm

New Member
Hi,

can u pls help me with the formula or steps for creating rolling month in Excel 2007.

e.g. starting month dynamic so that rest of the months in the plan can automatically rolled.


like if i change any of the month in cell(A1) the next month in (B1)should automatically change..
 
no Hui.... i am not getting it .... Do i need to type that formula in Cell - B1 ..?

Can u pls help me by step by step......
 
Hi Ramesh,


I am sure Hui will reply to you with a more robust solution. But, meanwhile, could you please try the below formula (at B1) for me?


=CHOOSE((IF((MONTH(DATEVALUE("01-"&A1))+1)=13,1,MONTH(DATEVALUE("01-"&A1))+1)),"January","February","March","April","May","June","July","August","September","October","November","December") enter.


Note: A1 is your month name (say, January), then the formula should display February. In a nutshell, whatever the month name you put in A1, the formula would display the name of next month.


Please let us know if this is fine.


Regards,

Kaushik
 
Put that in B1

Then copy across


In A1 enter 1/1/2007

Format cells to display dates on whatever format you want
 
Hi Kaushik ,


Your formula can be shortened a little more , using a construct I came across in this same forum , courtesy Haseeb :


=CHOOSE((IF((MONTH(A1&0)+1)=13,1,MONTH(A1&0)+1)),"January","February","March","April","May","June","July","August","September","October","November","December")


MONTH(A1&0) returns the number of the month e.g. if month is Sep or Sept or September or even Septem , the above will return 9.


Narayan
 
Thank you for your elegant touch Narayan...that is actually very good...I did not think of it...


Thank you once again...


Regards,

Kaushik
 
Good day kaushik03


With 01/01/2013 in A2 And this in B2 =EDATE( A2, 1 ) and copy across, EDATE also takes care of day dates if they are alters as well as the altered month
 
Thank you for your tips bobhc.


Actually I assume that OP has month name in the cell instead of complete date.


Anyways, how are you? Hope your New Year celebration was awesome.


Kaushik
 
kaushik03


Then if that's the case I bow down to your formula :)


Bit of a wee problem with the lungs over the holidays but I did my best to give support to the Scottish Whiskey Industries.
 
Hello Ramesh,


If you have a valid date in A1 & formatted to display as month, EOMONTH will work as Hui suggested.


If you have 'text' in A1, eg: Apr or April, here is another way.


In B1, then copy across.


=TEXT((A1&1)+31,"mmm")


Change the "mmm" to suitable. If you want to display months in reverse order, change +31 to -1


Hope this helps,

Haseeb
 
Hi Haseeb,


It's simply mind blowing, awesome...I don't have any more adjectives to express how much I do respect / admire your skills. This formula is even <10% in length of what I posted above.


That is why I call you as the "EXCEL MAGICIAN" . Share some more "jaw dropping" techniques and help me learn at least 10% of what, till date, you have learnt in excel.


Kaushik
 
Thanks to everyone for your kind response. Now i can see that its very easy ;). Thanks .... Thanks to all .......


Have a Gud Day..:)
 
@All

Although Haseeb's formula =TEXT((A1&1)+31,"mmm"), which is very clever, works for the Months Text

You need to be careful as it doesn't return the current year, it defaults to 2001 or consistent day of the month every month


In A2 put Apr

In B2 put =(A2&1)+31

Then format it as dddd, mmm dd, yy

Copy it across
 
Hello Hui,


In my previous post as mentioned "If you have a valid date in A1 & formatted to display as month, EOMONTH will work as Hui suggested" TEXT only better here, if want to return only MONTH.


A2 = Apr

in B2 I got 2-May-2013 with =(A2&1)+31 date serial 41396


But if we copy across this will become 41396&1, which is 20-Jun-3033, so on...
 
You must log in or register to reply here.
Back
Top