Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Could someone suggest how to calculate the no. of Saturdays between "Start Date" and "End Date". I also have a list of holidays. If the saturday is a holiday, then it should not get counted.
I am trying to get this at load time. So I have a table as follows:
Rec No. Start Date End Date Holidays.
Now I want to add another field through a script called NoOfSaturdays by calculating the no of Saturdays between "Start Date" and "End Date" excluding the holidays.
Any help would be appreciated.
Regards,
Rajiv
Hi Rajiv,
Try to use function: networkdays(start_date, end_date {, holiday} )
Regards,
Sokkorn
Hi Sokkorn,
Networkdays() excludes Saturdays and Sundays. Any similar function which counts only Saturdays?
Regards,
Rajiv
Hi Rajiv
There doesn't seem to be a similar function for Saturdays.
However, it may be useful to work on the basis that Saturday always follows Friday. So perhaps count the number of Fridays and then work from there. It might not be so easy to exclude holidays though.
Good luck.
Richard
Rajiv
If you include a holidays flag and a day of week field in a master calendar during, then this will be very easy to calculate. If you are not sure about a master calendar, search on this forum for master calendas there already a lot of articles.
To create a holiday flag, you will need to test the dates (when you build the calendar) against the list of holidays. You could do this be reading the holidays into a table with the date and holiday name, and left joining this to the master calendar.
Hope this helps
Jonathan
Good Morning Rajiv,
Use this script
Temp:
LOAD * Inline [
RecNo,StartDate,EndDate
1,01/01/2011,01/03/2011
2,03/05/2011,03/05/2011
];
SET vRecCount = NoOfRows('Temp')-1;
FOR i = 0 TO $(vRecCount)
vMinDate = NUM(PEEK('StartDate',$(i),'Temp'));
vMaxDate = NUM(PEEK('EndDate',$(i),'Temp'));
vCode = PEEK('RecNo',$(i),'Temp');
IF i = 0 THEN
recCount = 0;
ELSE
recCount = NoOfRows('TempCalendar');
ENDIF
TempCalendar:
LOAD $(vCode) AS RecNo2,
Date($(vMinDate) + RowNo()-$(recCount)-1) AS TempDate,
WEEKDAY(Date($(vMinDate) + RowNo()-$(recCount)-1)) AS MyWeekDay,
IF(WEEKDAY(Date($(vMinDate) + RowNo()-$(recCount)-1))='Sat',1,0) AS MyHoliday
AUTOGENERATE 1
WHILE $(vMinDate)+IterNo()-1<= $(vMaxDate);
NEXT
Regards,
Sokkorn