1 Reply Latest reply on Oct 8, 2010 8:06 AM by philmodjunk

    Working Days Calculations

    MadeleinMoolman

      Title

      Working Days Calculations

      Post

      Hi there

      I'm trying to work out a calculation / formula to work out the working days between 2 dates that excludes weekends and public holidays. I run a service centre and need to know how long repairs take from start to finish.

      I'm fairly competent in doing simple scripts but I have no formal training so I need help in simple terms.

      Please can someone help me with this?

      Thank you

      Madelein

        • 1. Re: Working Days Calculations
          philmodjunk

          Since the dates of Public Holidays change each year, you will need to maintain a list of such dates in a related table. Such a list can easily include the weekend dates as well. I find it best to set up a simple list or table view layout for managing these dates. A simple script can generate the weekend dates in this table and logging public holidays are simply a matter of creating a new record and entering the date.

          Your Holidays table, thus need only have a single date field to serve this purpose: HolidayDate.

          You can relate your parent table to the holiday table like this:

          ParentTable::Date1Field < Holidays::HolidayDate AND
          ParentTable::Date2Field > Holidays::HolidayDate

          Now you can define a calculation field in ParentTable that looks like this:

          Date2Field - Date1Field - Count ( Holidays::HolidayDate )

          To compute the working days from Date 1 to Date 2.