6 Replies Latest reply on Feb 14, 2013 2:29 PM by philmodjunk

    Calculating work days between two dates

    Matty_1

      Title

      Calculating work days between two dates

      Post

           I have a few questions in regards to calculating work days between two dates.  I followed the instructions posted in the following link and it doesn't quite work as I would expect it to.

           http://help.filemaker.com/app/answers/detail/a_id/5281/~/calculating-number-of-weekdays-(work-days)-between-dates

           First, say I have the start date on a monday and end date on a friday the returned calculation is 4 but my logic says the end date is inclusive therefor it should return 5.  A quick "+ 1" at the end of the calculation fixed this but I'm worried I'm missing something.  I tried understanding the entire calculation but can't seem to wrap my head around this one which is why I'm worried adding a + 1 arbitrarily at the end isn't smart.

           Second, the holiday exclusion assumes someone will never put a holiday as a start date but that doesn't mean a user may not mistakenly do so ... is there a way to fix this or that's the limitation of this current calculation?

        • 1. Re: Calculating work days between two dates
          philmodjunk

               The complex calculation (it used to have a bug in it but I helped FileMaker Inc find and fix it), does a kind of table lookup of data listed directly as part of the calculation. The + 1 adjustment to make the interval inclusive would seem exactly correct.

               I don't think there would be any trouble getting the correct count if a user specifies a holiday date as one or both of the dates in your interval. The calculation should still compute the number of week days and then subtract out the number of holiday dates from that total.

               BTW, i also know of an alternative approach: If you put all the dates of the calendar in a table and mark specific dates as "closed" whether because they are weekend dates or because it is a holiday, you can simply count all records on the interval that are not marked as "closed".

          • 2. Re: Calculating work days between two dates
            Matty_1

                 If I set the start date as a day before the holiday and end date as the holiday itself then my calculation outputs 1 day taken.   Same thing if I set the end date later that week, the calculation remains correct as long as the start date is before the holiday itself.

                 If I set the start and end date as the holiday my calculation outputs 1 ...  This is because the lookup subtracts the count by the same count number, it find an identical match for both days (Example New Year's day is 1, 1-1 = 0) there for that long calculation outputs 0 + 1 which I added at the end of the entire calculation and then - 0 from the holiday calculation ending in a result of 1 when it should be zero.

                 Last, if I set the start date as a holiday and end date as the following day, the result is 2.  Again the look up finds an identical match in the start date, doesn't find an identical match in the end date and defaults to the next lowest result in a - 0 calculation.

            • 3. Re: Calculating work days between two dates
              philmodjunk

                   The original work days calculation, with the added +1 to make the range inclusive works to calculate the correct number of work days, but there are better ways to structure the relationships to a Holidays table.

                   I'd have these fields in Holidays:

                   HolidayDate (date of holiday)
                   HolidayName (text)

                   Then I'd use this relationship to count the number of holidays within your date range:

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

                   Then your calculation for working days from StartDate to EndDate would be:

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


                   You can also set up a layout with a calendar format to manage holiday dates. I've adapted the method used in this demo file for that purpose in one of my solutions: https://www.dropbox.com/s/e8d03xvwe8vtz85/Calendar.fp7

              • 4. Re: Calculating work days between two dates
                Matty_1

                     Very cool, thank you Phil.

                • 5. Re: Calculating work days between two dates
                  Matty_1

                       I've implemented this and it works exactly as I want it to.  I know I thanked you already but thanks again!

                  • 6. Re: Calculating work days between two dates
                    philmodjunk

                         Note: I believe the original Knowledge Base article referenced here was created before inequalities could be used in a relationship.