DateOfWeek ( week ; yearOfWeek ; type )

Calculate the first date of a given week and year

Average rating: 4.0 (42 votes) Log in to vote

Reinder Wolting
ABECON
http://www.abecon.nl

Sample input:

DateOfWeek ( 1 , 2009 , 1 )

Sample output:

29-12-2008

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

//Calculate first date of a given week in a given year
// type 0 takes sunday as the first day of the week, type 1 takes monday
Let (
[
//the first of january of the given year
xFirstYearDate = Date ( 1 ; 1 ; yearOfWeek ) ; 
//the weekday number of the first of january of the given year
xFirstWeekDay = If ( type ; Choose ( DayOfWeek ( xFirstYearDate ) - 1 ; 7 ; 1 ; 2 ; 3 ; 4 ; 5 ; 6 ) ; DayOfWeek( xFirstYearDate ) )
];

//multiply the week number, make corrections and add it to the first day of the given year
xFirstYearDate + ( ( week - If ( xFirstWeekDay > 4 ; 0 ; 1 ) ) * 7 ) - xFirstWeekDay + 1 

)

When you only have the week number and a year and you need to know a date in that week, this function calculates the date of the first day of that week. It also takes in account the prefered starting day.

Comments

		Mark Rubenste, Evanston, IL Dec 29, 2014
This CF worked for week 1 last year (2014), but is one week off this year (2015). I think the the last line of code should be this: xFirstYearDate + ( ( week - If ( xFirstWeekDay > 6 ; 0 ; 1 ) ) * 7 ) - xFirstWeekDay + 1 NOT this: xFirstYearDate + ( ( week - If ( xFirstWeekDay > 4 ; 0 ; 1 ) ) * 7 ) - xFirstWeekDay + 1 In other words, > 4 should be > 6. Am I correct?

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

DateOfWeek ( week ; yearOfWeek ; type )

Comments

Support this website.

Support this website.