WeekRange ( _weekOfYear ; _year )

Given a week number and a year it returns the range of week's days.

Average rating: 4.3 (28 votes) Log in to vote

Daniele Raybaudi - Show more from this author
ACI
http://www.aci.it

Sample input:

WeekRange ( 1 ; 2012 )
WeekRange ( 2 ; 2012 )
WeekRange ( 53 ; 2012 )
WeekRange ( 54 ; 2012 )

Sample output:

01/01/2012
02/01/2012 - 08/01/2012
24/12/2012 - 30/12/2012
31/12/2012

Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

/*
WeekRange ( _weekOfYear ; _year ) Custom Function
Author: Daniele Raybaudi

Given a week number and a year it returns the range of week's days.
Not recursive and useful mostly for countries different from USA.
The result is correct for Monday as the starting day of the week.
For example:
week #1 of 2012 returns: 01/01/2012
week #2 of 2012 returns: 02/01/2012 - 08/01/2012
week #53 of 2012 returns: 24/12/2012 - 30/12/2012
week #54 of 2012 returns: 31/12/2012

*/

Let ([
firstDayOfYear = Date ( 1 ; 1 ; _year ) ; 
lastDayOfYear = Date ( 12 ; 31 ; _year ) ;
adj = firstDayOfYear + _weekOfYear * 7 - Choose ( DayOfWeek ( firstDayOfYear ) - 1 ; 7 ; 1 ; 2 ; 3 ; 4 ; 5 ; 6 ) ;
lastDayOfWeek = If ( Year ( adj ) = Year ( lastDayOfYear ) ; adj ; lastDayOfYear ) ; 
firstDayOfWeek = If ( Year ( adj - 6 ) = Year ( lastDayOfYear ) ; adj - 6 ; firstDayOfYear )
];
Case(
not _weekOfYear or _weekOfYear > 54 or ( firstDayOfWeek = firstDayOfYear and lastDayOfWeek = lastDayOfYear ) ; "" ;
firstDayOfWeek = lastDayOfWeek ; lastDayOfWeek ;
Substitute ( List ( firstDayOfWeek ; lastDayOfWeek ) ; ¶ ; " - " )
)
)

Given a week number and a year it returns the range of week's days.

Not recursive and useful mostly for countries different from USA.

The result is correct for Monday as the starting day of the week.

Comments

		Louis Voellmy, ZÃ¼rich Jul 1, 2013
Thx, just needed something like that! But the result is not always correct for Monday, as you write: WeekRange ( 1 ; 2014 ) returns 01/01/2014 - 05/01/2014 but the 01/01/2014 is a Wednesday. Grazie auguri Louis

		Raybaudi, Rome Jul 2, 2013
The american week start on sunday, but in Italy it start on Monday. The Custom function returns ( complete ) weeks starting at Monday, but the first day of a month can be whichever day of month.

		Roch Drouin, MontrÃ©al Jan 4, 2017
Hello, Does'nt seem to work anymore with WeekRange ( 1; 2012) nor WeekRange ( 1; 2017 ) . I use instead : /* WeekRange ( _date ) */ Let ( [ // Weeks start on Sunday dw = DayOfWeek ( _date ); startDate = _date - (dw - 1 ); endDate = startDate + 6 ] ; GetAsText ( startDate ) & " - " & GetAsText ( endDate ) )

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

WeekRange ( _weekOfYear ; _year )

Comments

Support this website.

Support this website.