cancel
Showing results for
Did you mean: Not applicable

## 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

1 Solution

Accepted Solutions  Luminary Alumni

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.

7 Replies  MVP

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)  MVP

Use MakeWeekDate().  Partner - Specialist II

Hi Manish,

Your expression will not work for years before 2000.  Partner - Specialist II

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

];  Luminary Alumni

Hi,

Try MakeWeekDate() function

=MakeWeekDate(Year, Week)

Example:

=MakeWeekDate(2014, 2)

Regards,

Jagan.  Luminary Alumni

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. Not applicable
Author

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 Community Browser