4 Replies Latest reply on May 18, 2015 8:02 PM by philmodjunk

    Calculate workdays in a month

    Stu412

      Title

      Calculate workdays in a month

      Post

      I'm looking to find a way to calculate the workdays in a given month, so just Monday to Friday.

      A lot of calcs I've seen rely on the user entering start and end dates.

      I'm hoping that FM has an intelligent calendar and is able to help in returning that June 2014 has 20 work days, for example. Ideally this would all be done automatically on the table with each record representing a separate month.  The use case is that a user then enters work hours directly into the portal for that table and, hey presto, we have their monthly hours with 12 portal rows.

      Any help appreciated, thanks

        • 1. Re: Calculate workdays in a month
          Stu412

          This happens occasionally - found some further answers which led to a solution.

          On a single row, you'll have a start and end date and the following formula to calculate days:

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

          I got this from the FM knowledgebase here:

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

          I must say that for July 2014, it's forgotten a day and has returned 22 instead of 23.  Seems to be consistently wrong where there are 31 days in a month.  

           

          Any idea why that may be?

           

          • 2. Re: Calculate workdays in a month
            raybaudi

            Try this calculation:

            Let([
            D = YourDateField ;
            startDate = Date ( Month ( D ) ; 1 ; Year ( D ) ) ;
            endDate = Date ( Month ( D ) + 1 ; 0 ; Year ( D ) )
            ];
            5 * Int ( ( EndDate - StartDate ) / 7 ) +Middle ( "0012345501234544012343340123223401211234010012340" ; 1 + 7 * (DayOfWeek ( StartDate ) - 1 ) + DayOfWeek ( EndDate ) ; 1 )
            )

            • 3. Re: Calculate workdays in a month
              Stu412

              Raybaudi,

              Thanks for this - it's working good for me now with your amended formula.  I don't pretend to fully understand what's going on with this particular formula (which is something I like to know), but I'm happy to take this!

               

              thanks

              • 4. Re: Calculate workdays in a month
                philmodjunk

                The calculation builds a table of values quoted string and then looks up a value from it. (I recognized it as I used to use that method to store a 3 dimensional array in a version of (not visual) BASIC that didn't support arrays of strings.

                Often the number of work days in a time interval has to subtract out any holidays that fall on that time interval. If such is the case, I find it simpler to use a table of holidays and adapt it to include weekend closures in the same table. Then it's simply a matter of using a relationship or ExecuteSQL to count related records that fall on a date range.