Hi Filemaker users,
Through which calculation can I get the date of Monday based on committed week number and year? For example based on week number 43 and year 2016 the date of Monday is 24-10-2016.
this custom function returns the date of the following Saturday (inclusive) of any date. You can adjust for your needs.
Explore some of the Date functions built into FM, especially: DayOfWeek( ); DayOfYear( ); WeekOfYear( ).
Thank you for the responses. I will look into them right now
I have Filemaker Pro which does not allow custom functions. Will this be a problem?
I also added an image so it is more clear what I hope to achieve.
Just to be certain my question is as follows: How can I have the date for Saturday until Friday entered through a calculation when "Weeknummer" and "Year" are filled with data?
To be able to create Custom Functions you need FileMaker Pro Advanced. If you do not have that, you can create your own calculation fields.
If your users enters date for when they work you can have Calculated fields downing many things. Here are a few example
DayOfWeek ( YourDateField ) - Gives you the number of that week, starting Sunday
Month (YourDateField ) - Gives you the month number for that date
WeekOfYear ( YourDateField ) - Gives you the week number of the year.
DayName (YourDateField) - Gives you Wednesday today
You can learn more about Date functions here
FileMaker Pro 15 Help
Then I recommend you to read the free FileMaker Training Series
FileMaker Training Series | FileMaker
Goedemiddag Barand, based on your screenshot I guess you're Dutch (like me) – or maybe Flemish.
It's worth noting that week numbering is different in the US and in Europe. In America, week 1 is the week that contains January 1st. In Europe we use the ISO standard, in which week 1 is defined as the first week containing at least 4 days of the new year.
The week boundaries are different, too. For Americans, Saturday is the last day of the week, while in Europe Sunday is considered part of the weekend, and thus, well, the end of the week.
Therefore, for us, week 1 of 2016 started on Monday 4 January.
The following formula calculates Monday from a given year and week number:
y = your_year_field ;
w = your_week_number_field ;
jan4 = Date( 1 ; 4 ; y ) ; // (4 january is always in week 1)
day0 = Int((jan4-1)/7)*7 // the (sun)day before the start of week 1
day0 + (w-1)*7 + 1 // the monday of week w of year y
For Tuesday etc., change the '+1' in the last line to '+2' etc.
Make sure the result of the calculation is of type Date.
HTH, succes ermee,
Oh, and don't forget: WeekOfYear( Date ) gives you the American week number, not the European one!
To get 'our' week number, use WeekOfYearFiscal( Date ; 2 ).
(The 2 in this formula means that Monday is used as the start of the week.)
A colleague gave me a solution, which is the following:
1. I created a Week and year data set in Excel:
2. Imported the Week and year data set in Filemaker
3. Created a relation between the Week and Year data set and the table in Filemaker
4. Created lookup fields in the table in Filemaker based on the first column in the week and year data set
That's a novel approach! Thank you for sharing, barand!
Retrieving data ...