Count the no. of Saturdays between two dates

[#1]

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

[Sokkorn]

Hi Rajiv,

Try to use function: networkdays(start_date, end_date {, holiday} )

Regards,

Sokkorn

[#3]

Hi Sokkorn,

Networkdays() excludes Saturdays and Sundays. Any similar function which counts only Saturdays?

Regards,

Rajiv

[richard_chilvers]

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

[jonathandienst]

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

[Sokkorn]

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

[#7]

Follow this link

Re: Count the number of saturdays & sundays between two dates