Search
Search

Convert 0FISPER to dd.MM.yyyy Format

Hi,

I want to get the last day of the month that I have entered to Prompt as a 0FISCPER in Webi 4.1 SP4 with using BICS query.

Value of UserResponse is return as Month Year format, for example "JUL 2015".

How can I convert it to dd.MM.yyyy format (01.07.2015)?

So, I would be able to use the LastDayofTheMonth function to get the last day.

My best Regards,

Zahid Yenerreplied

Hi Cihangir,

You are receiving error because your month names are in Turkish. WebI formulas work with English.

I did the followin formulas to get the last day of month in your case.

1. Create "Month Number" variable just like below:

=If Left([Takvim yılı / ay];3) = "OCK" Then "01"

ElseIf Left([Takvim yılı / ay];3) = "ŞBT" Then "02"

ElseIf Left([Takvim yılı / ay];3) = "MAR" Then "03"

ElseIf Left([Takvim yılı / ay];3) = "NİS" Then "04"

ElseIf Left([Takvim yılı / ay];3) = "MAY" Then "05"

ElseIf Left([Takvim yılı / ay];3) = "HAZ" Then "06"

ElseIf Left([Takvim yılı / ay];3) = "TEM" Then "07"

ElseIf Left([Takvim yılı / ay];3) = "AGU" Then "08"

ElseIf Left([Takvim yılı / ay];3) = "EYL" Then "09"

ElseIf Left([Takvim yılı / ay];3) = "EKM" Then "10"

ElseIf Left([Takvim yılı / ay];3) = "KSM" Then "11"

ElseIf Left([Takvim yılı / ay];3) = "ARL" Then "12"

2. Create "Year Number" variable like below:

=Right([Takvim yılı / ay];4)

3. Create "Month-Year" variable:

=[Year Number]+[Month Number]

4. Create "Month-Date" variable:

=ToDate([Month-Year];"yyyyMM")

5. Create "Last Day of Month" variable below:

=LastDayOfMonth([Month-Date])

0 View this answer in context