Reversing weekdates into 'regular' dates

[balooka]

Hi,

One of my clients needs a function in which he can enter a weeknumber and a calc will resolve that weeknumber to a valid date, in the current year.

The outcome of that calc must be so that it is always the sunday in that week. So if he enters 11, it should display the sunday (and date) of week 11, ignoring all other days...

I've been trying all sorts of things but i can't get the calc to work properly... I didnt even start on the 'sunday' thing yet since i can't get the weeknumber reversing done

any suggestions?

TIA

JP

[Ugo DI LUCA]

Hi Balooka,

You'll need the Year anyway as the result could be different for any year given, then :

Field yearSpecified (number)

Field weekSpecified (number)

1. Step 1 :

calculate first day of year given

firstDayOf Year = Date("01/01" & yearSpecified)

2. Step 2 :

Calculate first sunday for the year (according to the week - could be a sunday in end Dec 2002 while checking for 2003... EX: 1st Sunday for 2004 will be 12/28/2003)

firstSundayofFirstWeekofYear = firstDayOf Year - MOD(DayofWeek(firstDayOf Year) +6; 7)

Step 3 :

SundayOfWeekGivenofYear = firstSundayofFirstWeekofYear + ((Weekspecified -1) * 7)

or in one calculation

c_firstSundayofWeekofYear = Date("01/01" & yearSpecified) - MOD(DayofWeek(Date("01/01" & yearSpecified)) +6; 7) + ((Weekspecified -1) * 7)

[Ugo DI LUCA]

OOPS...

Just tested...

Set firstDayOf Year to Date("01", "01", yearSpecified)

[balooka]

Hi Ugo

This calc is not something I would have com eup with!

Just two words... Great & Thanks!

JP