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

    Count Day Names between 2 dates

    sccardais

      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
          keywords

          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
            karina

            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
              sccardais

              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
                siplus

                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)