7 Replies Latest reply: Jun 18, 2014 10:01 AM by Günter Angerer

# Convert week number and year to a standard date

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?

Regards

Günter

• ###### Re: Convert week number and year to a standard date

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)

• ###### Re: Convert week number and year to a standard date

Hi Manish,

Your expression will not work for years before 2000.

• ###### Re: Convert week number and year to a standard date

Use MakeWeekDate().

• ###### Re: Convert week number and year to a standard date

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

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;

Dt

05WO05

35WO04

07WO76

12WO84

];

• ###### Re: Convert week number and year to a standard date

Hi,

Try MakeWeekDate() function

=MakeWeekDate(Year, Week)

Example:

=MakeWeekDate(2014, 2)

Regards,

Jagan.

• ###### Re: Convert week number and year to a standard date

Hi,

Try MakeWeekDate() function

=MakeWeekDate(Year, Week)

Example:

=MakeWeekDate(2014, 2)

Data:

*,

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

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

AS Date

FROM DataSource;

Regards,

Jagan.

• ###### Re: Convert week number and year to a standard date

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