Splitting Month and Year from 'Monthyear' column

[Manmeet]

Hi All,

I need to fetch Month and Year seperately as a column from the given 'Fiscal Month' column.

Let me know your suggestions for the solution.

Thanks & Regards,

Manmeet Dhawan

[justISO · Accepted solution]

Hi, you can try to take left 3 symbols (as some months in your list is full month name, some are short 3 letter ones) as month and 2 right symbols as year and make date from them, like:

makedate('20'&right([Fiscal Month],2) , Month(Date#(left([Fiscal Month], 3), 'MMMM')) )

or if you need separately:

Year(makedate('20'&right([Fiscal Month],2)))

Month(Date#(left([Fiscal Month], 3), 'MMMM'))

[Or]

Adding to what was suggested by JustISO, Subfield([Fiscal Month],chr(39),1) and Subfield([Fiscal Month],chr(39),2) can be used to split up the two parts into a month name (textual) and a year (YY) field. Note that this assumes that you're using a regular single quote - if it's something else, you can replace the chr(39) with the appropriate character.

[MarcoWedel]

another solution might be:

Month(Date#([Fiscal Month],'MMM''YY'))

and

Year(Date#([Fiscal Month],'MMM''YY'))

hope this helps

Marco

[Manmeet · Accepted solution]

Thanks for your suggestion, It worked

Thank you !!

[Manmeet · Accepted solution]

Thanks for your suggestion, I tried ... This is also giving correct results.

Thank you !