YearWeek + Day to Date

[#1]

Is there some solution to convert a YearWeek + Weekday to a date?

My table has for Calendar 2 fields, YearWeek(201401, 201402, 201403) and Day Abbreviations(Mon Tue Wed)

This table is currently connected on YearWeek level to my Master Calendar, but because the other tables are linked through the date the master calendar is acting as a Link Table which gives me additional problems.

So my temponary solution is to create in all tables a unique key based on the YearWeek + dayname,

in the table that has only YearWeek and Day the code looks like YearWeek&'-'&Day

in the other tables it looks like Year(Date)&Week(Date)&'-'&WeekDay(Date)

this solves it for now but it is not the best solution in my opinion.

Is it possible at all to create a date based on the YearWeek + Day fields? and if so, how can i best approach this?

[#2 · Accepted solution]

Hi,

You can make a date from those 2 fields like this:

MakeWeekDate(Left(YearWeek,4),Right(YearWeek,2),Match(DayName,'Mon','Tue',...,'Sun')-1)

Hope it helps

[#3]

This is awesome!! it works, thank you very much.