excel formula in WEBI XI3

[Former Member]

Hello,

I would like to replicate the following excel formula in Webi XI3:

=year(a2)&"-"&if(isodd(month(a2)),month(a2)&"&"&Month(a2)+1,month(a2)-1&"&"&month(a2))

Could you please help me?

Thanks

[tanveer1 · Accepted solution]

Hi ,

Use below formula

= FormatNumber(Year([Date]);"0")+"-"+  If( Mod( MonthNumberOfYear([Date]);2) = 1 ) Then MonthNumberOfYear([Date]) + "&"+ ToNumber(MonthNumberOfYear([Date]) +1 ) Else MonthNumberOfYear([Date]) -1 +"&"+MonthNumberOfYear([Date])

Thanks,

Tanveer

[Former Member · Accepted solution]

Many thanks Tanveer for your prompt answer,

Unfortunately, things have to be more complicated than that

The problem is that my object is in string format not in date format.

Any idea how could I solve this issue?

Thank you in advance,

[former_member183904 · Accepted solution]

why cant you convert it into a variable using date format then convert using the above formula?

[Former Member · Accepted solution]

Amer Syed, I tried that but it seems it doesn't work:

I used the function "ToDate":

Thank you in advance for your reply

[former_member183904 · Accepted solution]

i dont think you can do that to year and month , you are missing date from it

look at this blog that explains on dates and it may guide you to what code you need to write. as it seems you are missing the DAY piece of the puzzle

[tanveer1 · Accepted solution]

Hi ,

Create a variable as Date with formula as below

Date = todate([Billing Month];"yyyyMM")

after that use the below formula as i suggested in the above reply

= FormatNumber(Year([Date]);"0")+"-"+  If( Mod( MonthNumberOfYear([Date]);2) = 1 ) Then MonthNumberOfYear([Date]) + "&"+ ToNumber(MonthNumberOfYear([Date]) +1 ) Else MonthNumberOfYear([Date]) -1 +"&"+MonthNumberOfYear([Date])

let me know how this works

Thanks,

Tanveer.

[Former Member · Accepted solution]

Thank you Tanveer. It works perfectly. Thank you once again!