to convert week-year to day-month-year format

[maternmi]

Hello,

the purchase order date is shown in the most cases as DD.MM.YYYY, but sometimes also as calendar week-year e.g. 43.2013. How can I convert it to a single date format (DD.MM.YYYY), to show the last day of the week. In this case cw 43.2013 should be convert in

25.10.2013

Thank you for your help!

Best regards,

Michael

[jagan]

Hi,

Try makeweekdate()

Regards,

Jagan.

[jagan]

Hi,

Try like this

=Alt(DateDimensionName, WeekEnd(MakeWeekDate( Right(DateDimensionName, 4), Left(DateDimensionName, 2)))

Hope this helps you.

Regards,

Jagan.

[Jason_Michaelides]

Try:

If(Len(PODate) <> 10

     ,MakeWeekDate(Right(PODate,4),Left(PODate,2),5)

     ,PODate)

                         AS     PODate

The third parameter given as "5" will return the Friday of the week. Adjust as desired.

Hope this helps,

Jason

[#5]

use this derived field into ur script

=if( Len(OrderDate)<=7,Date(WeekEnd(MakeWeekDate(SubField(OrderDate,'.',2),SubField(OrderDate,'.',1)))-2),OrderDate) as Orderdate1

Regards,

Kabilan K.

[maternmi]

I'm sorry, I haven't mention that I need a solution outside the script.

My dimension looks as follows

Delivery date (culomn name)

10.04.2012

11.07.2013

43.2013    --> Would like to have 25.10.2013

07.10.2013

16.2013   --> Would like to have 19.04.2013

Thanks & Best regards,

Michael

[Jason_Michaelides · Accepted solution]

You can use the same MakeWeekDate expression in the UI, but surely this would be much better corrected in the script?

[tresesco]

May be like attached sample?

[maternmi]

Hi Jason,

coul you please explain me the syntax? For better understanding.

Thanks!

Best regards,

Michael

[Jason_Michaelides]

From the F1 help:

MakeWeekDate(YYYY [ , WW [ , D ] ] )

Returns a date calculated from the year YYYY, the week WW and the day-of-week D.

If no day-of-week is stated, 0 (Monday) is assumed.

Examples:

makeweekdate(1999,6,6) returns 1999-02-14

makeweekdate(1999,6) returns 1999-02-08

In the solution provided, you first test the format of the current row value using Len().  If it is not 10 chrs long (DD.MM.YYYY = 10chrs) then use MakeWeekDate(),  Pass in the Year using Right(YourDate,4), pass in the Week using Left(YourDate,2) and define the day of the week you want to return the date for (omitted or 0=Monday).

Hope this helps,

Jason