Number of weekdays and weekend days between 2 dates

borderraux

Forums

Dear all ,
 
As per subject what is the easier way to find out the exact number of working days and weekend days that occur between 2 dates (regardles of public holidays).
Lets analyse the below example:
Start Date          End Date
02/01/2019        15/01/2019
Equation: End Date - Start Date
Result: 10 working days, 4 weekend days
I know that i could potentially use: @Dow and @Workday functions in IDEA but without counting first the number of days between both dates and then using nested Ifs statements I cannot see it working.
 
Any tips are much appreciated.
 
 
 
 

Steven Luciani Tue, 01/22/2019 - 22:10

Hello,

This can be done by creating two virtual or regular numeric fields.

To create the working day field assuming a M-F workweek you can use the custom function created by Brian Element in Feb, 2014. It is on the site but I've attached it again for you. If you've never used a custom function simply copy the file from this post to the Custom Functions.ILB folder of your project. Then in the equation editor you will find it in the custom functions group of the insert function pane. There are instructions of the parameters the function requires. Essentially it's just the start and end date fields.

Your next field weekend days can be created using the @age function and the working days field you just created. The equation would be @age(end_date,start_date)+1 - working_days(or whatever you called your first field) You need to add the +1 because the @age function counts from the end date back to the day before the start date exactly like the VB @datediff function works.

Brian will probably roll his eyes at the second part and wonder why I didn't create a Week_end days custom function. I did take a stab at it before posting this but just couldn't get the syntax correct, hence my work around

Good luck with your analysis.

 

borderraux Wed, 01/23/2019 - 07:28

Hi Steven,
 
Thank you for your answer.
I managed to move the function file above into idea custom function folder however when validating the function it generates the syntax error.
This is how the function line is displayedin in equation editior:
#weekday_dif(START_DATE,END_DATE) 
Both of my fields above seem to be correct from the data type point of view so I am not sure where the problem lies.
 
P.S. As i am relatively new to IDEA scripting where can i see the whole list of functions available that i can use in IDEAScript code module for scripting. I mean functions like: DateDiff or isplit etc. I wasnt able to locate such list anywhere neither in Language browser (BASIC SCRIPT,IDEA SCRIPT) nor in function list in equation editor.
Thank you

Brian Element Thu, 01/24/2019 - 07:42

Hi borderraux,

For the PS unfortunately not all visual basic functions are documents in the language browser.  I have tried to document some of them  in the VB language pages of the web site.  For the iSplit, certain equation editor functions can be used in IDEAScript by replacing the @ with an i, these are namely equation editor functions that were in place prior to IDEA V9, i use them quite a bit in my scripts.

Brian

borderraux Fri, 01/25/2019 - 05:03

Hi Brian, 
 
Thank you for your answer.
I noticed 'VB Language' tab on your page just after placing my post. I think its extremely useful list taking into consideration relative scarcity of information about VB functions in IDEA online. As a result I believe it is a matter of talking to experts like you/taking time to test individual function on daily basis by trial and error to build the knowledge of which VB functions can work correctly in IDEA.

Steven Luciani Wed, 01/23/2019 - 14:00

Hello,

I can't help you with the P.S. Brian or somebody on the forum that does more scriping that I do should be able to answer that question.

Regarding your syntax problem, one of two things must be happening. Either your start_date or end_date are not in IDEA date format. You can verify this in the equaiton editor by looking at the bottom of the window in the type column for your fields, or the virtual field your creating is not virtual or regular numeric. See screen shot below

If both of the field types are correct, then the only other thing I can think of is your environment variables in Windows are set to French so IDEA is looking for a ; paramater separater not a ,

 

borderraux Fri, 01/25/2019 - 05:08

Thank you Steven - as mention in my previous post the data type for fields I am working with are correct so that is not a problem here.
In terms of variables environment , i never came accorss such thing , but as I work for English company in London I would assume the whole operating system setup is referring to English rather than French.
I tried to change the variables setting myself but it proves difficult since i dont have administrator's priviledge for my machine. I will talk to IT to see if that might be an issue.

Steven Luciani Fri, 01/25/2019 - 07:14

Hello,

Don't get hung up on the ; parameter separater. I thought perhaps you were in France or using the French version of IDEA. I work in the Canadian government and we use both the English and French versions of IDEA and have computers setup by our IT department in both French and English and we run across this problem often.

I downloaded the custom function to try it myself and ran into trouble. I had to go back to Brian's original post and pull the one he put up a few years ago. I've attached it here. If this doesn't work, you can search for Brian's original post of the function by typing date_dif in the site search bar.

 

ravisdxb Sun, 01/27/2019 - 01:00

Please try this @Python function after copying the enlosed .py file to the Custom Functions folder of your project and  use it as @Python("Working_Weekend",DATE_COL1,DATE_COL2). It is almost similar to Brian's custom function, but, returns both working days and weekend days. You could get the same output by making a small modification to Brian's custom function.
 
Weekend assumed to be Saturday and Sunday. In Gulf countries, weekend  days are Friday and Saturday.
 
The attachment is actually .py and not .py.txt - please rename it to .py (file upload module is adding .txt by itself)
Regards,
Ravi

borderraux Mon, 01/28/2019 - 11:50

Hi Ravi,
Thank you for joining this feed and providing the function.At the moment I dont have ability to utilise Python functions as I dont have Python environment installed on my work machine. However I requested that (IT department needs to approve such request) and hopefully I will be able to see it at work.
I will let you know the outcome once I am able to run it.
 

Brian Element Mon, 01/28/2019 - 17:42

Which version of IDEA are you using?  As of 10.3 Python is builty into IDEA and you can run python scripts through the equation editor and via scripts.  To use what was attached you will need at least 10.3.