Convert YYYYMM date in to Mon-YY format.

building reports webintelligence
1

Hi,

In my report I have a date coming in Number format and it is in the form of YYYYMM.
I tried to convert in to Mon-YY format using below function but I am not getting the answer.

=ToDate(FormatNumber([YYYYMM-Date];"00");"MON-YY")

Can any one tell me where I am doing the mistake. Or the functions which I am using is not correct. I googled a lot but not found any correct soultion.

Cheers,
Chetan.

chetan86 :india: (BOB member since 2011-01-18)

2

Mon is no supported, Try this

=FormatDate(ToDate(FormatNumber([YYYYMM-Date];"0");"yyyyMM");"Mmm-yy")

Thanks,
Zaif

zaif235 :us: (BOB member since 2010-06-15)

3

Hi,

What is the datatype of object YYYYMM that you are getting? Is it date or number or string?

M H Mohammed :us: (BOB member since 2010-06-16)

4

Not sure, but you can try this-

=Month(Max(ToDate(FormatNumber([MonthID];"######"); "yyyyMM"))) + "  " + FormatNumber(Max(Year(ToDate(FormatNumber([MonthID];"######");"yyyyMM")));"####")

I had bit similar requirement, so worked on it. You can refer this-

Edit: You can remove Max from the code as it is not required in your case.
Updated one-

=Month(ToDate(FormatNumber([MonthID];"######"); "yyyyMM")) + "  " + FormatNumber(Year(ToDate(FormatNumber([MonthID];"######");"yyyyMM"));"####")

aniketp :uk: (BOB member since 2007-10-05)

5

YYYYMM is NOT a date. It is a year month number.
You need to make it a date.

=FormatDate(ToDate([YYYYMM-Date]*100+1;"yyyyMMdd");"Mmm-YY")

Similarly, Mon-YY is not a date format, it is a year month format - you are formatting the date and presenting it as a month. :+1: