Calculating Weekdays
Altair EmployeeHello 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
Answers
-
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.
0 -
This is a tough one. I have two questions for you:
- 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)?
- 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.
0 -
Altair Forum User said:
This is a tough one. I have two questions for you:
- 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)?
- 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.
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.
0 -
Altair Forum User said:
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.
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))
0 -
Altair Forum User said:
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))
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.
0 -
Altair Forum User said:
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.
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.
0 -
Altair Forum User said:
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.
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.
Are you using this same formula, or have you modified it?
0 -
Altair Forum User said:
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.
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.
0 -
Altair Forum User said:
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.
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.
0 -
Altair Forum User said:
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.
This would be perfect. However, the function is not listed when searching through "All Functions". Can you tell me how to find it?
0 -
Altair Forum User said:
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.
Also, if those default models have been previously deleted, is there a way to recover them.
0 -
Altair Forum User said:
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.
Grant, I got it finally! I had to do a reinstall to download the default models. Thanks for your help!
0 -
Altair Forum User said:
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.
Stephen, I finally got it using the user defined model. Thank you for taking the time to address my issue. You guys are awesome!
0 -
Altair Forum User said:
This would be perfect. However, the function is not listed when searching through "All Functions". Can you tell me how to find it?
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
0
