14 Replies Latest reply: Mar 24, 2017 10:41 AM by Grant Perkins RSS

    Calculating Weekdays

    Troy Rader

      Hello Datawatch Community.  I need help with calculating the number of weekdays between a start and end date.  I currently have Datawatch 13/13.5.  I  tried to use the user defined function, but I'm doing something wrong and don't exactly know how to use it/set it up.  Any help would be greatly appreciated.

       

      Thank you,

       

      Troy

        • Re: Calculating Weekdays
          Chris Porthouse

          If you are asking about how to create a user defined function, the best resource is going to be the built in help file inside of Monarch.  Click the question mark "?" in the upper right and on the search tab, look for "Configuring User-Defined Functions".  There are instructions there on how to configure them.

          • Re: Calculating Weekdays
            Stephen Smay

            This is a tough one. I have two questions for you:

            1. If the start date happens to be on a Saturday or Sunday, would you want that handled the same as if it was Friday (Monday would be one day away)?
            2. If the end date happens to be a weekend, would you want that handled as if it was Monday?

             

            Other than that, it shouldn't be too difficult to subtract two days for every week in between start and end dates... although I admit I haven't tried it yet, so may be a bit more complex than I'm thinking.

              • Re: Calculating Weekdays
                Troy Rader

                Thank you for the reply. To answer your question, the start date "should" always be a workday (Mon thru Fri).

                 

                I can calculate today() - 03/08/17 which would return a value of 8 (total days).  And, I'm trying to exclude Saturday and Sunday to give me a value of 6 (business days).

                 

                Thanks again for the input.

                  • Re: Calculating Weekdays
                    Stephen Smay

                    OK! I've got the formula for your situation where the dates will always occur on weekdays, and I put this on three lines for readability:

                    Age(StartDate, EndDate, 3) * 5

                    + (Weekday(EndDate) - Weekday(StartDate))

                    + If(Weekday(StartDate) > Weekday(EndDate), 5, 0)

                     

                    The Age function (with 3 as the interval) tells how many weeks have passed between the two dates, and we simply multiply that by 5 for the number of business days in a week.

                    Then we make use of the Weekday function, which returns 1 for Sunday, 2 for Monday, and so on... and add the difference between the end date and the start date.

                    Finally if the weekday of the start date is greater than the weekday of the end date (for example, start date is Friday and end date is Thursday) then it simply adds an extra 5 days, because the Age function only counts it as a full week if it makes it to the next weekday.

                     

                    If there is a situation where either start or end date may fall on a weekend, you will need to apply these two formulas to create adjusted start and end dates before applying the formula above. These will set start date weekend days to Friday and end date weekend days to Monday:

                    Adjusted Start Date: StartDate - If(Weekday(StartDate) = 1, 2, If(Weekday(StartDate) = 7, 1, 0))

                    Adjusted End Date: EndDate + If(Weekday(EndDate) = 1, 1, If(Weekday(EndDate) =7, 2, 0))

                      • Re: Calculating Weekdays
                        Troy Rader

                        I try using your formula and at first it seemed to work for me.  However, upon reviewing the data, it wasn't pulling correctly for me.  I may have done something wrong.  I calculated the number of weekdays from 3/10/17 to 3/17/17 and then also from 3/9/17 to 3/17/17.  The calculation gave me the same number of weekdays.

                          • Re: Calculating Weekdays
                            Grant Perkins

                            Troy,

                             

                            The UDF Functions should include this one in a standard install so long as the relevant reference model is available.

                             

                            Is this what you are using?

                             

                            _WorkingDays_ComputeNormalWorkingDaysInRange({10/3/2017},{17/3/2017})

                             

                            If so and if you are testing by entering the dates manually (note these dates are d/m/y for my locale) you will need to use the {} brackets to identify them as dates.

                             

                            If you are using variables defined as dates (field values for example) you should be OK.

                             

                            In 13.5 this gives me 5 or 6 days depending on whether the start date is 10th or 9th.

                             

                            Does this help?

                             

                            If you don't see this function in the list of functions available let us know. We may need to go and find it in your installation.

                              • Re: Calculating Weekdays
                                Troy Rader

                                This would be perfect.  However, the function is not listed when searching through "All Functions".  Can you tell me how to find it?

                                  • Re: Calculating Weekdays
                                    Grant Perkins

                                    Troy,

                                     

                                    Try "User Defined Functions" (fx) in the "Table design" screen. Or "Add" then Function.

                                     

                                    By default the base file for the functions included with the installation is usually here:

                                     

                                    C:\Users\Public\Documents\Datawatch Monarch\Models\UserDefinedFunctions.dmod

                                     

                                    If you don't have it anywhere another copy of the file can be installed. Any model can be used. If you have have an xmod model form an earlier version of monarch that can be imported  - not too sure how much may have changed but I suspect probably not much - it is quite comprehensive in what it offers.

                                     

                                    Basically it is an "External Model" and you can add external models and manage what you want from them from within the fx feature.

                                     

                                    HTH

                                     

                                     

                                    Grant

                                  • Re: Calculating Weekdays
                                    Troy Rader

                                    Also, if those default models have been previously deleted, is there a way to recover them.

                                    • Re: Calculating Weekdays
                                      Troy Rader

                                      Grant,  I got it finally!  I had to do a reinstall to download the default models.  Thanks for your help!

                                    • Re: Calculating Weekdays
                                      Stephen Smay

                                      Troy, I'm not sure what's happened. I just tried it again using the same formula with the dates you indicated, and am getting the correct result.

                                      Weekdays Expression.PNG

                                      Weekdays Calculated.PNG

                                      Are you using this same formula, or have you modified it?

                                      • Re: Calculating Weekdays
                                        Stephen Smay

                                        Grant is right! There is already a function to computer normal working days in range... That's a lot simpler than my formula. I've learned something new.

                                          • Re: Calculating Weekdays
                                            Grant Perkins

                                            Just a word of caution here.

                                             

                                            The calculation can deal with non-working days  - typically national holidays  - and any other days that are deemed to be "non-working days" as well as weekends.

                                             

                                            That's great but these things are often variable and the function needs the information about variability, year by year, in order to deliver ultimate accuracy.

                                             

                                            If using it "in anger" check out all of the related functions and double check the notes in the definitions to ensure that you have defined the variables as you need them to be.

                                             

                                            For most people this will just be an annual update but some requirements may make specifics of the inclusion and exclusions be subject to greater monitoring and variation.

                                             

                                            Come to think of it this subject (and the wider concepts of the UDFs)  might be worth a webinar or at least a good solid video or two.

                                            • Re: Calculating Weekdays
                                              Troy Rader

                                              Stephen, I finally got it using the user defined model.  Thank you for taking the time to address my issue.  You guys are awesome!