3 Replies Latest reply on May 8, 2014 12:42 PM by philmodjunk

    calculating the number of work days less vac/holidays

    jbrogers1234

      Title

      calculating the number of work days less vac/holidays

      Post

           I have accomplished this with an earlier post using a start date end dated ... but when you have multiple fields that you need to determine the number of days between the two dates my solution fails??? not sure why, I have relational tables both between the startdates and enddates and between the main table and the tables where my "startdates" are located. Happy to provide moe info if it would help

        • 1. Re: calculating the number of work days less vac/holidays
          philmodjunk

               Well for one thing, you haven't described the solution that you tried to use. There's more than one possible method you might set up for this.

               

                    but when you have multiple fields that you need to determine the number of days between the two dates my solution fails???

               What do you mean by "multiple fields" why would you need more than a start and end date?

          • 2. Re: calculating the number of work days less vac/holidays
            jbrogers1234

                 thanks philmodjunk for your reply:  here is the solution I was trying to employee:

                 StartDate (Date)

                 EndDate (Date)

                  

                 Define a calculation field (WorkDays) with a number result with this formula:

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

                 -----------------------------------------------------

                 If a holiday (of one or more days) occurs between StartDate and EndDate, and you don't want to count the holiday as a work day, then some changes are required. First, create a new Table, HOLIDAYS, which contains the following fields:

                  

                 Date (Date)

                 Counter (Number)

            Holiday (Text) - Optional

                 By multiple fields I mean I have a number of different variables each with a potentially different start date.  my ultimate goal is to calculate the number of days and the determine cost

            • 3. Re: calculating the number of work days less vac/holidays
              philmodjunk
                   

                        By multiple fields I mean I have a number of different variables each with a potentially different start date.

                   Yes but each of those should use the same two fields, but in different records. That way the same set up calculates each number of work days and you have several options for calculating a total from a group of such records.

                   Here in one my working systems we use a slightly less convoluted approach. I set up a table with one record for every day of the year and mark all the days that the business is closed--whether due to a holiday or just because it is Sunday. I'm able to designate certain days of the week as "always closed" and I can click controls or enter  a date into a special date field  to add additional dates closed for holiday purposes.

                   I then use a relationship that only matches to records for dates that the business is open and count the total number of records.