Need help with formula to calculate holidays

[SmokeEater]

I have been trying to get this formula to work but I can't get it to maintain the number of holidays that is calculated when the year changes. Here is the code.
PS. I tracked down most of the coding thanks to Tek-Tips.

//Main formula
WhileReadingRecords;
Local DateVar Start := Minimum({?MyDate}); //{StartDate};   // place your Starting Date here
Local DateVar End := Maximum({?MyDate}); //{EndDate};  // place your Ending Date here
Local NumberVar Weeks; 
Local NumberVar Days; 
Local Numbervar Hol;
DateVar Array Holidays;

Weeks:= (Truncate (End - dayofWeek(End) + 1 - (Start - dayofWeek(Start) + 1)) /7 ) * 5;

Days := DayOfWeek(End) - DayOfWeek(Start) + 1 + 
(if DayOfWeek(Start) = 1 then -1 else 0)  + 
(if DayOfWeek(End) = 7 then -1 else 0);   

//Holiday Calculation
numberVar yr := Year ({polegs.locsched}); // The year for which you need Holidays


redim Holidays [12];

holidays [1] := CDate (yr, 1, 1);     // New Years day
holidays [2] := {@GoodFridayHoliday}; // Good Friday
holidays [3] := holidays [2]+3;       // Easter Monday
holidays [4] := CDate (DateAdd ("d", 25 - DayOfWeek (CDate (yr, 5, 11), crMonday), CDate (yr, 5, 1) ) );
                                      // Victoria Day (4th Mon in May)
holidays [5] := CDate (yr, 7, 1);     // July 1 (Canada Day)
holidays [6] := CDate (DateAdd ("d", 8 - DayOfWeek (CDate (yr, 8, 1) , crMonday), CDate (yr, 8, 1) ) );
                                      // Civic Holiday (first Mon in Aug)
holidays [7] := CDate (DateAdd ("d", 7 - DayOfWeek (CDate (yr, 9, 1) , crTuesday), CDate (yr, 9, 1) ) );
                                      // Labor Day (first Mon in Sept)
holidays [8] := CDate (DateAdd ("d", 15 - DayOfWeek (CDate (yr, 10,1), crMonday), CDate (yr, 10,1) ) );
                                      // Thanksgiving (2nd Mon. in Oct)
holidays [9] := CDate (yr, 11, 11);   // Armistice Day
holidays [10] := CDate (yr, 12, 25);   // Merry Christmas
holidays [11] := CDate (yr, 12, 26);   // Boxing Day


Local NumberVar i;
For i := 1 to Count (Holidays)
do (if Holidays[i] in start to end then Hol:=Hol+1 );

Weeks + Days - Hol

 

[synapsevampire]

Consider building a period table, it's the method used by data warehousing and more sophisticated databases.

Check my FAQ:

faq767-4532

It also resolves other reporting issues, as the title states.

-k

 

[ronbowen]

SmokeEater,

Have you looked at kenhamady.com. Ken's formulas for calculating weekends and holidays are very good.

 

[SmokeEater]

Yes, I did. I was able to resolve my problem by breaking things down to two formulas. This is what I ended up with for my holidays.

WhileReadingRecords;
Local DateVar Start := Minimum({?MyDate}); // place your Starting Date here
Local DateVar End := Maximum({?MyDate}); // place your Ending Date here
Local NumberVar YearStart; 
Local NumberVar YearEnd;
Local NumberVar YearCount;
Local NumberVar Weeks; 
Local NumberVar Days; 
Local Numbervar Hol;
Local Numbervar Array Hol1;
DateVar Array Holidays;


YearStart := Year(Start);
YearEnd := Year(End);
YearCount := (YearEnd-YearStart)+1;
numberVar yr := YearStart;

Local NumberVar j;
For j := 1 to YearCount
do 
(
yr:=YearStart;

redim Holidays [12];

holidays [1] := CDate (yr, 1, 1);     // New Years day
holidays [2] := {@GoodFridayHoliday}; // Good Friday
holidays [3] := holidays [2]+3;       // Easter Monday
holidays [4] := CDate (DateAdd ("d", 25 - DayOfWeek (CDate (yr, 5, 11), crMonday), CDate (yr, 5, 1) ) );
                                      // Victoria Day (4th Mon in May)
holidays [5] := CDate (yr, 7, 1);     // July 1 (Canada Day)
holidays [6] := CDate (DateAdd ("d", 8 - DayOfWeek (CDate (yr, 8, 1) , crMonday), CDate (yr, 8, 1) ) );
                                      // Civic Holiday (first Mon in Aug)
holidays [7] := CDate (DateAdd ("d", 7 - DayOfWeek (CDate (yr, 9, 1) , crTuesday), CDate (yr, 9, 1) ) );
                                      // Labor Day (first Mon in Sept)
holidays [8] := CDate (DateAdd ("d", 15 - DayOfWeek (CDate (yr, 10,1), crMonday), CDate (yr, 10,1) ) );
                                      // Thanksgiving (2nd Mon. in Oct)
holidays [9] := CDate (yr, 11, 11);   // Armistice Day
holidays [10] := CDate (yr, 12, 25);   // Merry Christmas
holidays [11] := CDate (yr, 12, 26);   // Boxing Day
////holidays [12] := CDate (yr + 1, 1, 1); // New Years next year (may fall on the 31st of this year)



Local NumberVar i;
For i := 1 to Count (Holidays)
do (if Holidays[i] in start to end then Hol:=Hol+1 );

YearStart :=YearStart+1
);

Hol