6 Replies Latest reply: May 15, 2014 10:07 AM by Nick Osdale-Popa RSS

    Number of specific workdays in a date range

    Kathi _

      I am attempting to calculate the number of a specific workday in various date ranges.  For example, in the date range of 8/20/10 through 10/8/10, how many Friday's (F) are there (inclusive)?  I have many different date ranges and the day of the week will potentially vary by row.  By looking at a calendar, I know that the answer to the first row is 8.  However, the data population is too large to figure it all out by using a calendar and counting!  By the way - Saturday is an option in the list but Sunday is not.

       

      An example of the data is:

       

      Day     Start Date     End Date

      F     8/20/2010     10/8/2010

      M     8/16/2010     8/18/2010

      TH     8/17/2010     9/2/2010

      TH     8/17/2010     9/9/2010

      M     8/16/2010     10/13/2010

      W     8/16/2010     10/13/2010

      T     8/17/2010     10/7/2010

      T     10/19/2010     12/7/2010

      M     10/14/2010     12/13/2010

      T     10/14/2010     12/13/2010

      M     11/3/2010     12/13/2010

      /CODE

       

      Any help would be appreciated!

        • Number of specific workdays in a date range
          Olly Bond

          Hello Kathi,

           

          I see from your profile that you're using Monarch 10.5 - so you should be able to solve this problem fairly easily. There is a new feature in Monarch (introduced in v9 or v10) called "User Defined Functions". One of these is designed to handle weekdays and is included in the UserDefinedFunctions.xmod model that you'll find in the standard models folder.

           

          To access it, open your report and model, then go to the Table window. Click on the f(x) icon in the toolbar to call up the UDF dialog. Click on "import" and navigate to UserDefinedFunctions.xmod. You'll see a list of functions and you can import the ones you need into your model. You'll need to import 9 functions - one called "WorkingDays" and eight others prefixed _WorkingDays.

           

          HTH,

           

          Olly

            • Number of specific workdays in a date range
              Kathi _

              Thanks for the response.  I already had those functions installed but I can't seem to get one to do what I need.  Obviously, I must be missing something.  Can you help me figure out what I need the formula to be?

                • Number of specific workdays in a date range
                  Olly Bond

                  Hello Kathi,

                   

                  Assuming you've got the UDFs imported into your model, then I would have thought that you could get the answer you need for each day by setting that day, say, Friday, to be the only working day of the week, then getting a count of the number of working days in the interval you need?

                   

                  If you could email me your report and model to olly@greenbar.info[/email] I'll happily have a look.

                   

                  Just built a model from your sample report, and if you create a lookup field that maps the day letter (F, TH, etc) to numbers (Sunday = 1, Monday = 2 etc), then the UDF gives you the function: CountOccurrencesOfWeekdayInRange(DayNumber,[Start Date],[End Date])

                   

                  Hopefully that should be just what you need.

                   

                  F     2010-08-20      2010-10-08     6     7

                  M     2010-08-16      2010-08-18     2     1

                  TH     2010-08-17      2010-09-02     5     2

                  TH     2010-08-17      2010-09-09     5     3

                  M     2010-08-16      2010-10-13     2     9

                  W     2010-08-16      2010-10-13     4     8

                  T     2010-08-17      2010-10-07     3     8

                  T     2010-10-19      2010-12-07     3     7

                  M     2010-10-14      2010-12-13     2     8

                  T     2010-10-14      2010-12-13     3     8

                  M     2010-11-03      2010-12-13     2     5

                  /CODE

                   

                  HTH

                   

                  Olly