Extract month from a number

[flupsie]

my financial year start in Oct being period 1 and Ends in sept being period 12


I would like to extract the month from the period number and also include the year. numbers 1 to 3 would be for the prior year for eg 1 would be Oct 2017 and 4 for eg would be January 2018


I have tried to use the choose formula, but it returns 10

Please Login or Register  to view this content.

your assistance in resolving this is most appreciated

[Phuocam]

2 optional:

1.=TEXT('Pivot Table'!B2+93,"mmm ")&TEXT('Pivot Table'!B2,"yyyy") result -> Jan 2017


2.=TEXT('Pivot Table'!B3+93,"m/")&TEXT('Pivot Table'!B3,"yyyy") result -> 1/2017

[avk]

For more clarity plz upload sample file with required result before & after.

[flupsie]

Thanks Phuocam


Your formula works perfectly

[flupsie]

Hi Avk


thanks for your reply. would like to see if there is a way to use the choose formula , although Phuocam kindly provided me with a solution that works using an alternative formula

See attached sample data. The Choose Formula which I tried to set up is on on sheet2

[avk]

let clarify : If period mentioned in summary 8 then what result required in sheet2
give us 3-4 examples.

[flupsie]

Thanks for your reply

Start period is Oct

1 = Oct, = 2 Nov 3 = Dec , 4= Jan etc


In my sample Data 8 must result in May + Current year i.e May 2018, if 1, 2 or 3 must be the month + previous year for eg if 1 in in cell b2 on summary sheet, the formula on sheet2 must result in Oct 2017


Your assistance in this regard is most appreciated

[Phuocam]

=CHOOSE(MONTH('Pivot Table'!B2),"Oct ","Nov ","Dec ","Jan ","Feb ","Mar ","Ap r","May ","Jun ","Jul ","Aug ","Sep ")&YEAR('Pivot Table'!B2)

[flupsie]

Thanks Phuocam


for the time being I have excluded the year, but the month does not change on Sheet 2 A1 when I enter the number in B2 in Sheet Pivot Table


Kindly check & amend

[Root_]

Actually, you don't need the MONTH() in the formula:

=CHOOSE('Pivot Table'!B2,"Oct ","Nov ","Dec ","Jan ","Feb ","Mar ","Apr ","May ","Jun ","Jul ","Aug ","Sep ")

You can also try this shorter one:

=TEXT((MOD('Pivot Table'!B2+8,12)+1)*28,"mmm ")

[flupsie]

thanks for your help root.

The formula extracts the months perfectly