I have a formula that calculates working days between two dates and excludes holidays. My problem is two fold..
I would like to create a database table of holiday dates, which I can then read into my formula so I only need to maintain the data in a single location and the formula becomes more managable
FYI I am using Crystal 10 & my formula is listed below (with the list of holidays cut down)...
// Start Date Time and Date
Local DateTimeVar dt1:= {@DateFSComplete}; //Date Time
Local DateTimeVar d1:= CDate({@DateFSComplete}); //Date
// End Date Time & Date
Local DateTimeVar dt2:= {@DateInvoiced}; // Date Time
Local DateTimeVar d2:= CDate({@DateInvoiced}); //Date
//Convert Days to Seconds
Local NumberVar ds:= (Date(dt2) - Date(dt1))*86400;
//Convert Hours to Seconds
Local NumberVar hs:= (Hour(dt2) - Hour(dt1))*3600;
//Convert Minuites to Seconds
Local NumberVar ms:= (minute(dt2) - Minute(dt1))*60;
//Seconds
Local NumberVar ss:= Second(dt2) - Second(dt1);
//Total Seconds
Local NumberVar ts:= ds+hs+ms+ss;
//Extract total days from total seconds
ds:= Truncate(ts/86400);
//Extract total hours from total seconds
hs:= Truncate((Remainder(ts,86400))/3600);
//Extract total minuites from total seconds
ms:= Truncate((Remainder(ts,3600))/60);
//Extract remaining seconds
ss:= Truncate(Remainder(ts,60));
//Remove Weekends
Local NumberVar Sat:= 0;
Local NumberVar Sun:= 0;
Local NumberVar WD:= 0;
Sat := DateDiff ("ww", d1, d2, crSaturday) ;
Sun := DateDiff ("ww", d1, d2, crSunday);
WD := Sat + Sun;
// *** Remove Bank Holidays ***
// Set Bank Holidays to 0
Local Numbervar bankhols := 0;
//BANK HOLIDAYS HERE
//Loads of bank holidays removed
//2008 BANK HOLIDAYS
//New Years Day (01/01/2008)
if date(2008,01,01) in d1 to d2 then bankhols := bankhols + 1;
//Good Friday (21/04/2008)
if date(2008,03,21) in d1 to d2 then bankhols := bankhols + 1;
//Easter Monday (24/04/2008)
if date(2008,03,24) in d1 to d2 then bankhols := bankhols + 1;
//May Bank Holiday (05/05/2008)
if date(2008,05,05) in d1 to d2 then bankhols := bankhols + 1;
//Spring Bank Holiday (26/05/2008)
if date(2008,05,26) in d1 to d2 then bankhols := bankhols + 1;
//August Bank Holiday (25/08/2008)
if date(2008,08,25) in d1 to d2 then bankhols := bankhols + 1;
//Christmas Day (25/12/2008)
if date(2008,12,25) in d1 to d2 then bankhols := bankhols + 1;
//Boxing Day (26/12/2008)
if date(2008,12,26) in d1 to d2 then bankhols := bankhols + 1;
//Calculate days (days - holidays - Weekends) - Note Global variable used here to alllow other formulas to perform calcaulation on figure in FD (cont.)
//If more than one of these formulas, give each final days values a unique name
Global NumberVar FD;
FD:= (DS - bankhols - WD);
StringVar Display:= ToText(FD,0,"") + ":" + ToText(hs,0,"") + ":" + ToText(ms,0,"") + ":" + ToText(ss,0,"");
Display