Convert week number and year to a standard date

[#1]

I'm struggling with a date format that I would like to convert...

The format that I have from the data source is like this: 05WO05

05 = week number

WO = the abbreviation for "week" in german

05 = the year, i.e. 2005 (data goes back to 1993!)

My goal is a format that will enable to me to have the data sorted by month and calendar year.

Any idea?

Many thanks for your help!

Regards

Günter

[MK_QSL]

Something like this

Month

=MONTH(Date(MakeDate(NUM(Right('05WO05',2)+2000))+(WeekStart(Left('05WO05',2))-1)*7))

Year

=Year(Date(MakeDate(NUM(Right('05WO05',2)+2000))+(WeekStart(Left('05WO05',2))-1)*7))

Date

=Date(MakeDate(NUM(Right('05WO05',2)+2000))+(WeekStart(Left('05WO05',2))-1)*7)

[tresesco]

Use MakeWeekDate().

[manojkulkarni]

Hi Manish,

Your expression will not work for years before 2000.

[manojkulkarni]

Below is the script to generate weekDt, Month & Year for the given data. Assumption is data starts from 1950 onwards.

Load *, Month(WeekDt),Year(WeekDt);

Load *, MakeWeekDate(Yr,WkNo) AS WeekDt;

Load *,

Left(Dt,2) AS WkNo,

if(right(Dt,2)>0 AND right(Dt,2)<15,(2000+right(Dt,2)),

if(right(Dt,2)>50 AND right(Dt,2)<99,(1900+right(Dt,2)))) AS Yr;

LOAD * INLINE [

    Dt

    05WO05

    35WO04

    07WO76

    12WO84

];

[jagan]

Hi,

Try MakeWeekDate() function

=MakeWeekDate(Year, Week)

Example:

=MakeWeekDate(2014, 2)

Regards,

Jagan.

[jagan · Accepted solution]

Hi,

Try MakeWeekDate() function

=MakeWeekDate(Year, Week)

Example:

=MakeWeekDate(2014, 2)

Data:

LOAD

*,

MakeWeekDate(If(Num(Right('05WO93', 2)) >= 93, 1900, 2000) + Num(Right('05WO93', 2)),

Num(Left('05WO93', 2)))

AS Date

FROM DataSource;

Regards,

Jagan.

[#8]

Jagan,

thank you very much - the MakeWeekDate approach was perfectly right. The only thing that needed to be changed was the 93 to 92 as I had some dates ranging back to 1992, which I did not notice myself. I was able to create now a complete date, a month+year and a year date field.

MakeWeekDate(If(Num(Right(Lieferzusage, 2)) >= 92, 1900, 2000) + Num(Right(Lieferzusage, 2)),

     Num(Left(Lieferzusage, 2)))

     as Lieferdatum,

     MonthName(MakeWeekDate(If(Num(Right(Lieferzusage, 2)) >= 92, 1900, 2000) + Num(Right(Lieferzusage, 2)),

     Num(Left(Lieferzusage, 2)))) as Liefermonat,

     YearName(MakeWeekDate(If(Num(Right(Lieferzusage, 2)) >= 92, 1900, 2000) + Num(Right(Lieferzusage, 2)),

     Num(Left(Lieferzusage, 2)))) as Lieferjahr,

Thank you and all the other guys who responded so unbelievable fast.

Regards

Günter