9 Replies Latest reply on Oct 28, 2016 2:19 PM by beverly

    Show dates based on week number and year

    barand

      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.

       

      King regards,


      Baran

        • 1. Re: Show dates based on week number and year
          David Moyer

          Hi,

          this custom function returns the date of the following Saturday (inclusive) of any date.  You can adjust for your needs.

          Capture.PNG

          • 2. Re: Show dates based on week number and year
            keywords

            Explore some of the Date functions built into FM, especially: DayOfWeek( ); DayOfYear( ); WeekOfYear( ).

            • 3. Re: Show dates based on week number and year
              barand

              Thank you for the responses. I will look into them right now

              • 4. Re: Show dates based on week number and year
                barand

                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?Show date for days in certain week number and year.png

                • 5. Re: Show dates based on week number and year
                  Johan Hedman

                  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

                  • 6. Re: Show dates based on week number and year
                    peterbouma

                    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:

                     

                    Let([

                      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,

                    Peter Bouma

                    1 of 1 people found this helpful
                    • 7. Re: Show dates based on week number and year
                      peterbouma

                      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.)

                      2 of 2 people found this helpful
                      • 8. Re: Show dates based on week number and year
                        barand

                        Back again.

                         

                        A colleague gave me a solution, which is the following:

                         

                        1. I created a Week and year data set in Excel:Week and year.PNG

                        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

                        • 9. Re: Show dates based on week number and year
                          beverly

                          That's a novel approach! Thank you for sharing, barand!

                          beverly