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!
Dank je Peter, exactly what I was looking for.
In addition to that: the confusion about week 53. It does exist but not every year, but in: 2015, 2020, 2026, 2032, 2037, 2043, 2048 and 2054.
Faq: Weeknummers; Veel gestelde vragen over weeknummers (sorry, in Dutch)
Rather than "reinventing the wheel" in a FMP-only CF, I would use a modern off-the-shelf API.
Here's all the Java code you need below. You don't need FMP Advanced and you could even call this code from any application that can do a GET!
Nice. clean. Easy to understand. Object oriented. Etc. Of course, in a micro-service you write, these hard-coded items would be variables.
And, you can easily pick TUESADAY, or another day of the week.
The code prints: The Monday for year: 2016 Is 2016-10-24
Plus, with the last line above, you have lots of other TemporalAjusters options along the same lines as your question:
HOPE THIS HELPS.
Retrieving data ...