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.
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
Then I recommend you to read the free FileMaker Training Series
1 of 1 people found this helpful
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,
2 of 2 people found this helpful
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:
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!