Was looking around for a good formula for working day calculation between two dates. The prolem was that we have Saturdays also as working. After going through various posts, I came across the following post
([Sent on]-[Upload Date]) - The difference between the dates
((if(Year([Sent on])-Year([Upload Date])>0,if(WeekStart([Sent on])<>WeekStart([Upload Date]),Week(yearend([Upload Date]))+(Week([Sent on])-Week([Upload Date])),0)- This to get the Sundays between the days since I want to exclude them.
I needed to check the years above to offset the weeks for subsequent years. (add all the weeks of the current year to the difference in the weeks since the subsequent year will start with 1 again.
(Networkdays([Upload Date],[Sent on])-Networkdays([Upload Date],[Sent on],$(strAllHolidays))) - networkdays with and without holidays were taken into consideration just ot get the holidays. Hoilidays are defined in a variable in a script taken from an excel file.
This works great for me but would be a problem if the diference between the dates spans more than a year.
The advantage it gives me is that for some clients specific saturydays are non working (1st and 3rd). In that case, I just add them in the holiday list.
First, is this the best approach. Any better suggestions. Any thoughts on working over multiple years
In this way it is possible to take one date's number minus another to get the difference in working days. Add holidays to an Excel-file and check for example Saturdays and Sundays with an if() as they are always holidays (?).
SET vFirstDate = date#('2009-01-01','YYYY-MM-DD'); SET vLastDate = date#('2010-06-01','YYYY-MM-DD');
mHolidays: mapping LOAD date#(Date,'YYYY-MM-DD') as Date,1 INLINE [ Date 2009-01-15 2009-03-18 2009-05-12 2010-01-18 ];
Dates: Load date($(vFirstDate)+rowno()-1) as Date autogenerate($(vLastDate)-$(vFirstDate)+1);
Dates2: Load Date, rangesum(peek(DateNo), if(match(num(weekday(Date)),5,6) // Saturdays+Sundays non-working or applymap('mHolidays',Date,0)=1, // Lookup holiday 0, // Add zero as it is holiday 1) // Add one as it is working day ) as DateNo resident Dates; drop table Dates;
You indeed have a unique perspective to the solution. But according to your solution, I would have to have some date range to start of with in the script. This might be limiting. It would be great if there is solution which is open to the start date and end date being any date in any year. Please correct me if I am wrong.
In this case, would it not be better to pr-calculate at least some data in your script like the upload date - sent date etc. You can also write a custom vbscript/jscript function to make some of the calculations easier (vbscript has got a native datediff() function). Drawback of that is that those functions cannot be referenced in a chart (as far as I know ---> enhancement? ). Some pre-calculation in the script will eventually make your calculations in the chart simpler and thus faster.....
I agree with you. My thoughts also were that baseed on Morgan's approach, within the script, I could get the minimum date and maximum dates. There are many date fields (dates trapped for different stages in the process). All of them are in different fields. But I could always get the minmum and maximum across the fields eventually within the script.
I would tend to agree that doing so within the script would make it less intesive during chart calculations, especially if a very large recordset is involved.
Another thing that I thought of in your solution is how to map all the date numbers in case of multiple date fields in the same record in a table. Would you then be joining the date numbers for each date field. Would this not be too expensive to do?