Date Range From Week

[ShawnR]

How can I get the date range from a week number?

I have a report that gives weekly totals based on the week number. I would like to also give the date range for that given week number.

Any help would be great!

Thanks

Shawn

[fabriceN]

// week/year sunday

week * 7 + Date( 1; 1; year ) - DayOfWeek( Date ( 1 ; 1; year )) - 6

where

- 6 -> sunday

- 5 -> monday

- 4 -> tuesday...

[ShawnR]

This works well. You just have to check if in Jan and Dec that it does not wrap into the next or previous year.

Let ( [

Start_Date = Report_Week * 7 + Date ( 1 ; 1 ; Report_Year ) - DayOfWeek ( Date ( 1 ; 1 ; Report_Year ) ) - 6 ;

End_Date = Report_Week * 7 + Date ( 1 ; 1 ; Report_Year ) - DayOfWeek ( Date ( 1 ; 1 ; Report_Year ) )

] ;

Case ( Year ( Start_Date ) < Report_Year ; Date ( 1 ; 1 ; Report_Year ) ; Start_Date ) & " - " &

Case ( Year ( End_Date ) > Report_Year ; Date ( 12 ; 31 ; Report_Year ) ; End_Date )

)

Thank you very much!

Shawn

[fabriceN]

Well, it depends. I think we don't count the weeks the same way on both banks of the Atlantic River :

Anyway, since Santa Claus comes on the 24th of december there and on the 5th here... what can you expect ?

More seriously, ISO 8601 states that : (translated by me)

First week of a year (week number 1) is the one that contains the first Thursday of the year.

In other words, it's the week the 4th of January belongs to.

The last week of the year is the one directly followed by the first one (I love that !)

FileMaker's WeekOfYear is not ISO compliant. It returns the american standard, where week number 1 is the one where the 1st of January is.

The Continental Europe standard (I don't know for the big island in the middle) states that the first week which is fully in January is week number 1, but I'm not 100% sure of this one.

Note also that our weeks start on Monday, because we like to have weekends at week ends : (we also like to play "football" with feet : )

HTH

Edited November 28, 2007 by Guest