Report Date

Hello
I have an object called reporting period which brings in MAY 2004. I need to convert this to show it as 05/04. I tried Todate(,“mm/yy”) and I get an #Error in the cell.

Can someone please suggest how I can get this to working?

Thanks


ddsrini (BOB member since 2004-06-23)

May be you need to convert this MAY 2004 to 01-MAY-2004 using character operators and then apply TO_CHAR (Oracle SQL) or FormatDate(BusObj Report)…

Mohan


cpmohanraj :australia: (BOB member since 2002-09-23)

Is this a text field with defined months? It looks like you have a month, a space, and then the year not a date field.


Randy Jones :us: (BOB member since 2002-08-19)

The string format for the ToDate function is the INPUT format. It tells the ToDate function how to interpret your character string. You need something like this to convert your string to a date:

ToDate(<Reporting Period> ,"Mmm yyyy")

At this point you can simply format the cell to display mm/yy. The other alternative is to wrap the above in another function:

=FormatDate(ToDate(<Reporting Period> ,"Mmm yyyy") ,"mm/yy")

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

When you use the Business Objects Reporter function ToDate the parameter expected is the existing format of the Date string (and not the target formattting), for example:

ToDate ("12/31/2004" , "mm/dd/yyyy")
ToDate ("Nov-3o-2004", "Mmm-dd-yyyy")

Then you can use FormatDate.


Andreas :de: (BOB member since 2002-06-20)

Thanks a lot DWayne. That worked.


ddsrini (BOB member since 2004-06-23)

You’re quite welcome :+1:


Dwayne Hoffpauir :us: (BOB member since 2002-09-19)