format(#01-01-2006#;"yyyy-ww";2;3) results in 2006-52 instead ...

[easyit]

..of of 2005-52.

Which in itself is logical but not what I need.

How do you get the right week with the right year?

 

[easyit]

...to clarify a bit more, in the calendar that i need ti use the first of January 2006 belongs to the last week of last year. So when format is returning the right week, I need to have the (to the week) corresponding year, not the year that that is in the given date.

 

[mp9]

Can you use the Weekday function to return a number (1 to 7, obviously) for the day of the week that your date is, use that to calculate a number to subtract from your date value, then format the result.

This is untested and not very well thought out (!) but something like:

format(#01-01-2006# - (7 - Weekday(#01-01-2006#)), "yyyy-ww", 2, 3)

In effect this has the effect of working out the date of the first day of the week that the test date falls in. Which I think is what you need...

Even if this looks like it works you might want to play around with other dates around New Year (a bit of boundary analysis testing, you might say) to make sure all is well. Note that Weekday() also takes a second parameter to specify what day of the week you consider to be the first which, looking at the parameters you've specified on the format function, you might need to play with.

http://www22.brinkster.com/accessory/