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

# Number of specific workdays in a date range

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

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

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

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

• ###### Number of specific workdays in a date range

I got it.  Thanks so much for your help.  This is such a valuable resource for all Monarch uers.  I appreciate your help!

• ###### Number of specific workdays in a date range

Just an alternative way of doing this without having to use a Lookup field and making the formula all-in-one:

CountOccurrencesOfWeekdayInRange(Instr(Day,"MTWTHF")+1,StartDate,EndDate)[/B]

• ###### Number of specific workdays in a date range

Well, I did some more thinking about this, and although my formula does work, it can lead to problems when adding in weekends. It could still be done inclusively, but takes a little more work. Best just to stick with Olly's suggestion of the lookup field.