Changing format of Date field in Webi

building reports webintelligence
1

Hi

I need to convert the following datefield: ‘JAN-2001’ to be ‘JAN-01’ in a Webi report in 3.1.

I tried using this formula; =FormatDate([MonthYear];“MMM-YY”) but I’m getting the following error;

‘The expression or sub-expression at position 12 in the ‘FormatDate’ function uses an invalid data type. (WIS 10037)’

Thanks!

Blowers (BOB member since 2009-09-30)

2

Is MonthYear a date or a string?

Damocles :uk: (BOB member since 2006-10-05)

3

Thanks for replying!

MonthYear is a variable that i created that combines the year and month field

Blowers (BOB member since 2009-09-30)

4

try using

= Replace([MonthYear],"20","")

Alternatively, if you have separate Year and Month fields, you can use

a) if month is a month number of year

=FormatDate(ToDate("" + [Year] + "/" + [Month] + "/01"; "YYYY/MM/DD"); "MMM-YY")

b) if month is specified as 3-char string:

=[Month] + "-" + Right(""+[Year];2)

Dmitry Grekov :ru: (BOB member since 2009-09-29)

5

Welcome to B:bob:B! Could you please post the code you used to concatenate Month and Year?

Jansi :india: (BOB member since 2008-05-12)

6

This is what i used to combine month & year…

=[Month Abbreviated Name]+"-"+[Accounting Calendar Year]

Blowers (BOB member since 2009-09-30)

7

so it’s time to change it for

=[Month Abbreviated Name]+"-"+Right(""+[Accounting Calendar Year];2)

Dmitry Grekov :ru: (BOB member since 2009-09-29)

8

Dmitry,

=[Month Abbreviated Name]+"-"+Right(""+[Accounting Calendar Year];2)

Is working!!

Thanks for your help!!!
Much appreciated…

Blowers (BOB member since 2009-09-30)

9

Gooseman, the reason why you got error is, you are trying to format a string using FormatDate(), where as you’ve to format a Date. So try converting the string to Date using ToDate() and then use FormatDate() function to avoid the error. Alternatively you can also Dmitry’s code.

Jansi :india: (BOB member since 2008-05-12)

10

Gooseman, fine :smiley:

The method Jansi refers to (using ToDate() function) is described in my first message in this stream (look at the 2nd code dlock).

Dmitry Grekov :ru: (BOB member since 2009-09-29)

11

Use: =ToDate(“Jan-2001”; “Mmm-yyyy”)
To covert it to a date, then right click and format to Mmm-yy

You could use the FormatDate function, but it will convert the date back to a string. May not be a problem, but will not sort chronological.

Robert Schmidt (BOB member since 2002-11-15)