13 Replies Latest reply on Dec 16, 2015 8:12 AM by jdevans

    WeekOfYear function acting strange

    jdevans

      I have a field that allows the user to select a date via a calendar drop-down object. I have a second field that reads the date selected on the first field and it uses the WeekOfYear function to show the current week # on the layout. I noticed scrolling thru the weeks, that it shows this week as #51, next week #52 and the week after that as #53 (that begins in December). Then the very next week (first full week of January shows as #2.

       

      There is no week #1 for January.

       

      Any idea why that might be?

        • 1. Re: WeekOfYear function acting strange
          Extensitech

          12/31/15 is in week 53 of 2015.

           

          1/1/16 is in week 1 of 2016.

           

          While both days fall in the same week, they do not fall in the same WeekOfYear because they are in different years.

           

          Chris Cain

          Extensitech

          • 2. Re: WeekOfYear function acting strange
            steveromig

            I have a field that allows the user to select a date via a calendar drop-down object. I have a second field that reads the date selected on the first field and it uses the WeekOfYear function to show the current week # on the layout. I noticed scrolling thru the weeks, that it shows this week as #51, next week #52 and the week after that as #53 (that begins in December). Then the very next week (first full week of January shows as #2.

             

            There is no week #1 for January.

             

            Any idea why that might be?

             

            Not sure if this helps, but if you enable the display the week numbers in the Calendar app on Mac OS it shows the exact same thing.

             

            Assuming the Calendar app is correct also - it is a little easier to visualize, you can see that it makes sense.  The only confusing part, but I suppose it makes sense because it is a new year, is that Thursday, 12/31 shows "53" and Friday, 1/1 shows "1" even though they are the same week.  So Friday and Saturday (the 1st and 2nd) would technically be the first week of 2016 a and the first full week would be the 2nd week.

             

            So if you enter in either 1/1 or 1/2, WeekOfYear will return "1" in FileMaker Pro.

             

            Steve Romig

            FileMaker, Inc.

            • 3. Re: WeekOfYear function acting strange
              jdevans

              thanks for providing clarification.

              • 4. Re: WeekOfYear function acting strange
                hbrendel

                This is all incorrect. 1, 2 and 3 january are week 53. 4 > 10 january are week 1. My Calendar application in Mac OS X indicates this correctly.

                 

                I just tested this in FM, indeed FM is wrong. This means that in 2016 all the week numbers are incorrect. Another unusable function in FM.

                • 5. Re: WeekOfYear function acting strange
                  jdevans

                  I just tried it on a new database file. I created 1 table with 2 fields. 1) A date entered field and 2) a week num field. The week num field is a calculation using weekOfYear of the the date entered field.

                   

                  jan1 and jan2 2016 both calculate to week 1

                  jan3 (Sunday) calculates to week 2.

                   

                  This looks correct to me.

                  • 6. Re: WeekOfYear function acting strange
                    jdevans

                    sorry, I typed it wrong. Sunday is Jan 4. Sat is Jan3. Sat Jan3 does in fact calculate to week1 on my test database file.

                    • 7. Re: WeekOfYear function acting strange
                      jdevans

                      Oops, I had switched it to 2015. Either way for 2015 or 2016, it seems to work as expected, now that I understand what it is doing.

                      • 8. Re: WeekOfYear function acting strange
                        jdevans

                        weekOfYear.PNG

                        Looks right to me.

                        • 9. Re: WeekOfYear function acting strange
                          MaxEh

                          To add more fuel to the week consider the following:

                           

                          Week number according to the ISO-8601 standard, weeks starting on Monday. The first week of the year is the week that contains that year's first Thursday (='First 4-day week'). The highest week number in a year is either 52 or 53. This year (2015) has 53 weeks according to ISO

                           

                          This is not the only week numbering system in the world, other systems use weeks starting on Sunday (US) which for 2015 has 52 weeks or Saturday (Islamic).

                           

                          So your week number may vary.

                           

                          Martin

                          • 10. Re: WeekOfYear function acting strange
                            hbrendel

                            It's not correct.

                            • 11. Re: WeekOfYear function acting strange
                              Extensitech

                              Different <> Wrong.

                               

                              FileMaker, your calendar app on your OS, and ISO apparently come up with (potentially) three different answers. That doesn't tell us which one is "right" and which are "wrong", or even lead to the logical conclusion that any of them are "wrong". From them being different, we can only conclude that they're different. FileMaker functions will enable you to determine weeks according to the other two, so I'd tend to favor the flexible one, not pronounce it "unusable". By your same logic, I could pronounce mac's calendar as "unusable" because not only does it come up with the "wrong" week number, there's no way to change it.

                               

                              Personally, I'd question how 1/1/16 could be "week of year" 53, when it's the first day of the "year" in question.

                               

                              But really, I'd like to question the usefulness of breaking down our calendar arbitrarily into 7-day chunks, then into chunks of 28, 29, 30 or 31. I'm not sure, starting from there, how we could ever come up with a "weekofyear" that's "usable" for all situations. Perhaps we should speed or slow the earth's rotation, or the orbit of earth around the sun so we can get a nice round number to work with.

                               

                              Chris Cain

                              Extensitech

                              • 12. Re: WeekOfYear function acting strange
                                hbrendel

                                I looked into this. We have to use the function WeekOfYearFiscal ( date ; 2 ). Then the result is correct.

                                • 13. Re: WeekOfYear function acting strange
                                  jdevans

                                  "Different <> Wrong."

                                   

                                  TRUE