4 Replies Latest reply on Jun 28, 2016 10:37 AM by siplus

# Count Day Names between 2 dates

I would like to count the number of Mondays, Tuesdays, etc between 2 dates (including start and stop dates) and excluding Holidays listed in a separate table. This is an example of the output I'd like.

I've found a lot of custom functions that return the total number of days, excluding weekends or excluding holidays but I haven't found anything that will show a count of the number of days by day of the week.

FYI - this is to create a schedule of classes held on one or more days every week and that span several months. I want to account for the fact that some months have 5 Mondays (for example) while others don't.

Anyone have any experience doing something like this?

• ###### 1. Re: Count Day Names between 2 dates

Have a play around with the Date functions. DayName ( ) will return the name ofd whatever day you include as a parameter in the brackets. DayOfWeek ( ) will return a number from 1 to 7. Monday, I think, is 2.

• ###### 2. Re: Count Day Names between 2 dates

Hi,

If you create a relationship with a date table with all the dates in a year.

Create the fields in that table

Flag Sunday, Field Type Number, Formula / Entry Option, Auto-enter calculation: = Case (DayOfWeek ( Date ) =1; 1;0), replaces existing value
Flag Monday, Field Type Number, Formula / Entry Option, Auto-enter calculation: = Case (DayOfWeek ( Date ) =2; 1;0), replaces existing value
Flag Tuesday, Field Type Number, Formula / Entry Option, Auto-enter calculation: = Case (DayOfWeek ( Date ) =2; 1;0), replaces existing value Etc.
You can also create a field Flag holiday in this table.

And then create a sum.Let me know if this works you and if it makes sense or if you want to make a sample for you.

• ###### 3. Re: Count Day Names between 2 dates

Thanks, Karina.

I’m very tempted to ask you to make the sample but I want to try this myself! No pain, no gain!

Scott

• ###### 4. Re: Count Day Names between 2 dates

You could also define a gCounter global repeating field, 7 reps, and go looping from \$currentDate = beginDate to EndDate, inside the loop just 2 lines,

Set Variable [\$index; dayofWeek(\$currentDate)]

Set Field [gCounter[\$index] ; gCounter[\$index] + 1

and at the end you have your 7 counters in your gField.

(obviously you will need to zero the gCounter every time, to start with)