3 Replies Latest reply on Jul 25, 2015 6:44 PM by philmodjunk

    Calculating Business days

    MatthewMorgan_1

      Title

      Calculating Business days

      Post

      Hello all!

      Brand new to FM coming from an Excel VBA background, so i have some limited understanding of code. I have been trying to use the formula i find everywhere to calculate business days:

      5 * Int ( ( Date End- Date Start) / 7 ) +Middle ( "0012345501234544012343340123223401211234010012340" ; 7 * (DayOfWeek ( Date Start) - 1 ) + DayOfWeek ( Date End) ; 1 )

      The results are... sequential nonsense. 1 day separation = 7141, 2 days = 14287, and so on. I know I'm missing something dumb, i just cannot for the life of me figure it out. Any help would be greatly appreciated.

      i have attached a sample image of the simple form I'm using.

       

      Calc.JPG

        • 1. Re: Calculating Business days
          philmodjunk

          Are your date fields really date fields and not text or number fields?

          Did you set this ups as a calculation field or a number field with an auto-enter calculation?

          And are weekdays all that you need to account for? Or do you also need to adjust this value to account for any holidays between your start and end dates?

          The calculation--which could be defined as a custom function works for me.

          • 2. Re: Calculating Business days
            MatthewMorgan_1

            The date dropdowns were set as text instead of dates blush

            Thank you so much! I knew it was something ridiculous. My next step is to use the other formula and set up my holidays :)

            Thanks again!

            • 3. Re: Calculating Business days
              philmodjunk

              But if you are going to include Holidays, I wouldn't use this method at all. I find it easier to set up a calendar table with one record for each day and then mark the days as either "open" or closed. An auto-enter calculation can mark weekend dates closed automatically and you can seven set up an actual calendar format with portals such that you can just click a button or enter a date into a global date field and run a script, to find and mark a given date as a holiday.

              Then a relationship that matches to only dates with an "open" status in the date range specified can be used to count the number of days--which now counts all working days that are not also holidays.