Increment Month in Excel and convert to Text

3
votes

I am trying to calculate fiscal months from a date and convert it to text in Excel 2010 and use the formula as a Site Column in SharePoint 2010.

Our fiscal month runs from the 19th of one month to the 18th of the next month.

So, for example if the the date is 12/19/2012, the fiscal month would be January.

I used the following formula, but I all I get as a result is Jan.

=IF(DAY(A2)>=19,TEXT(MONTH(A2)+1,"mmm"),TEXT(MONTH(A2),"mmm"))

Any help is appreciated.

Thank you.

2
exceltextincrement

2 Answers

2
votes

You shouldn't be using MONTH function because your formula is returning a number 1 to 12 which is deemed to be a date in January 1900, hence you always get Jan, use EDATE to increment the month like this:

=IF(DAY(A2)>=19,TEXT(EDATE(A2,1),"mmmm"),TEXT(A2,"mmmm"))

....or you can use this shorter version for the same result

=TEXT(EDATE(A2-18,1),"mmmm")

That will give you December for 18th December but January for the 19th - clearly you can use "mmm" if you just want the short form of the month

0
votes

You need to use mmmm for the full name.