Jump to content
Sign in to follow this  
ShawnR

Date Range From Week

Recommended Posts

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

Share this post


Link to post
Share on other sites

// week/year sunday

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

where

- 6 -> sunday

- 5 -> monday

- 4 -> tuesday...

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 by Guest

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.