Month....

[dawa]

How do i get this.

If i put January in one cell the other cell goes two month back i.e November. If i put October, then August and so on.

 

[Faseeh]

Hi dawa,

Try this in B1, if A1 contains the month:

=IF(MATCH(A1,Months,0)=1,OFFSET(F1,12+MATCH(A1,Months,0)-3,0),OFFSET(F1,MATCH(A1,Months,0)-3,0))

Where Months is a named range, from F1:F12 containing name of months. See this as well

 

[Xiq]

Or this:

=TEXT(DATE(2010,MONTH(DATEVALUE("1 "&A1))-2,1),"mmmm")

Where A1 is the month in text (e.g.: January)

 

[AVK]

This also try. Refer attach file.

 

[NARAYANK991]

Hi Xiq ,

This is one from Haseeb : =MONTH(A1&0) will return the month number e.g. if A1 has January , it will return 1.

Your formula can become : =TEXT(DATE(1,MONTH(A1&0)-2,1),"mmmm")

Narayan

 

[Xiq]

Hi Xiq ,

This is one from Haseeb : =MONTH(A1&0) will return the month number e.g. if A1 has January , it will return 1.

Your formula can become : =TEXT(DATE(1,MONTH(A1&0)-2,1),"mmmm")

Narayan

Nice! To bad blanks will also been seen as a month

 

[dawa]

Thanks everyone but i found out the simple way using vlookup... i just want the text formats. But the below idea came from you all.....

jan nov
feb dec
mar jan
apr feb
may mar
jun apr
jul may
aug jun
sep jul
oct aug
nov sep
dec oct

 

[Haseeb A]

Also,

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

Or,

=TEXT((1&A1)-56,"mmm")