10 Replies Latest reply on Apr 5, 2016 12:33 PM by drted

    KB Article "number of weekdays between two dates" has a bug

    Peabody

      Summary

      KB Article "number of weekdays between two dates" has a bug

      Product

      FileMaker Pro

      Version

      11.0v4

      Operating system version

      Mac OS X 10.6.8 & 10.7.2

      Description of the issue

      The custom function listed at http://help.filemaker.com/app/answers/detail/a_id/5281/~/calculating-number-of-weekdays-(work-days)-between-dates returns incorrect results.

      Steps to reproduce the problem

      Use a start date of 11/3/2011 and end date of 11/10/2011.

      Expected result

      The expected result is 5.

      Actual result

      The actual result is 14.

        • 1. Re: KB Article "number of weekdays between two dates" has a bug
          philmodjunk

          Minor quibble: These are simply calculations, not custom functions.

          There's a typo in the calculation. If you fix that typo, it should work for you:

          Change:

          5 * Int ( ( EndDate - StartDate ) / 7 ) +Middle ( "0012345501234544012343340123223491111234010012340" ; 7 * (DayOfWeek ( StartDate ) - 1 ) + DayOfWeek ( EndDate ) ; 1 )

          To:

          5 * Int ( ( EndDate - StartDate ) / 7 ) +Middle ( "0012345501234544012343340123223401111234010012340" ; 7 * (DayOfWeek ( StartDate ) - 1 ) + DayOfWeek ( EndDate ) ; 1 )

          • 2. Re: KB Article "number of weekdays between two dates" has a bug
            TSGal

            Peabody:

            Thank you for your post.

            There is definitely a typo in the calculation, and a big thanks to PhilModJunk for pointing this out and correcting it.  The article has been updated.

            TSGal
            FileMaker, Inc.

            • 3. Re: KB Article "number of weekdays between two dates" has a bug
              Porpoise

                   This calculation is very useful. It would be nice if the text makes it more clear that StartDate is included and EndDate is not included in the number of workdays. I would prefer to include both StartDate and Enddate, but that is easy to correct.

                   Unfortunately, the additional calculation for the holidays is not compatible as it includes holidays on EndDate but not on StartDate. That is a bit more difficult to fix as a Lookup is used. One should make Lookup Low check the day before StartDate if holidays on StartDate are to be included, and Lookup High should lookup the day before EndDate if EndDate is to be excluded. Or add a calculated field to the Holiday table with a date that is shifted 1 day up (later) from the original holiday date, and use that field for the Lookup. Somebody with more experience than I, please check this suggestion.

                   Finally, please add to the text a warning that holidays that occur on Saturday or Sunday should not be added to the table.

              • 4. Re: KB Article "number of weekdays between two dates" has a bug
                TSGal

                     Porpoise:

                     Thank you for your post.

                     The article has been updated.  Just below the HOLIDAYS table, the following has been added:

                     "NOTE: Only include those holidays that occur during a work day; not weekends."

                     To StartDate and EndDate are to both be included, then just add one more day to the result.

                     TSGal
                     FileMaker, Inc.

                • 5. Re: KB Article "number of weekdays between two dates" has a bug
                  Porpoise

                       Thanks. Filemaker must be going for a new record in responsiveness. smiley

                       To include EndDate too isn't as easy as adding one more day to the result. The EndDate could be in a weekend (or a holiday). For calculating the workdays, you have to add one day to both occurrences of EndDate in the calculation. Not too difficult, but it would be nice to explain in the text that EndDate itself doesn't count.

                       The real problem is that for finding holidays, StartDate is excluded and EndDate is included. This is wrong. For Monday December 22, 2014 till Thursday December 25, 2014 the result will be 2 working days as Christmas on EndDate gets subtracted from the result. And Thursday January 1, 2015 till Saturday January 3, 2015 will return 2 working days  as New Year on StartDate is ignored. That is, if I understand the algorithm correctly. I quoted the explanation from the article with a changed StartDate. 

                  "These two fields determine the number of holidays that have occurred between StartDate and EndDate. For example, StartDate = 7/5/07 and EndDate = 7/15/07. 7/5/07 looks up into HOLIDAYS, and a match is found, and 5 is returned from Counter. 7/15/07 looks up into HOLIDAYS 2, and no match is found. However, the next lower value is 7/5/07 (Independence Day Observed), and 5 is returned from Counter. Subtracting Lookup Low from Lookup High (5-5=0) does not return the number of Holidays that have occurred between 7/5/07 and 7/15/07."

                  • 6. Re: KB Article "number of weekdays between two dates" has a bug
                    philmodjunk

                         To count records in Holidays, use this relationship to the Holidays table:

                         YourTable::StartDate < Holidays::Date AND
                         YourTable::EndDate > Holidays::Date

                         Or you can use a script or custom function to fill a text field with a list of all dates from StartDate to EndDate and use this relationship:

                         YourTable::DateList = Holidays::Date

                         PS. This thread does not automatically appear in recent items when someone posts a comment to it. Thus it is easy to miss unless you happen to be subscribed to it and get an emailed copy of the comment. To make sure that you get maximum visibility for your comment, you might want to start a new thread and post a link back to this thread so that your comments pop up in Recent Items.

                    • 7. Re: KB Article "number of weekdays between two dates" has a bug
                      Porpoise

                           Thank you, PhilModJunk. Nice solution. I changed the relationship to the Holidays tabel as you suggested, and then created

                           NumberOfHolidays      Calculation     Unstored, from YourTable, = Count ( Holidays::Date )

                           Unfortunately, this gave inconsistent results. It might have something to do with caching old results, that I don't really understand. Changing it to

                           ... Unstored, from YourTable, = ExecuteSQL ( "select count (*) from Holidays where Date between ? and ?" ; "" ; "" ; StartDate ; EndDate )

                           worked.
                            
                           P.S. As long as this discussion pops up in Google and the original article gets updated, everybody should be fine.
                      • 8. Re: KB Article "number of weekdays between two dates" has a bug
                        Porpoise

                             Ouch. There is still a typo in the article's string "0012345501234544012343340123223401111234010012340".

                             It should read "0012345501234544012343340123223401211234010012340".

                        • 9. Re: KB Article "number of weekdays between two dates" has a bug
                          TSGal

                               Porpoise:

                               Thank you for pointing this out.  The article has been updated again, where both references to the array string have been updated.

                               TSGal
                               FileMaker, Inc.

                          • 10. Re: KB Article "number of weekdays between two dates" has a bug
                            drted

                            TSGal,

                            Might it be more accurate to say:

                            "To have the StartDate and EndDate both included, add 1 to the Endate"

                             

                            Instead of adding 1 to the final result?

                             

                            Would this formula work?

                             

                            5 * Int ( ( (EndDate + 1) - StartDate ) / 7 ) +Middle ( "0012345501234544012343340123223401211234010012340" ; 7 * (DayOfWeek ( StartDate ) - 1 ) + DayOfWeek ( EndDate + 1 ) ; 1 )