Calculate Start Date from Lead time and Due Date

building reports crystal reports
1

I have a Part Order crystal report that displays the due date and lead time for a part to be ordered. i need to calculate the start date according to those fields excluding weekends and holidays. any suggestions?

Example:
Due Date: 9-15-2008
Lead Time: 5 Days
Start Date: 9-8-2008 (This is the date 5 days back excluding weekends and holidays from the due date) i need this formula.

Thanks,
Paige

:stupid:

sarahpaige (BOB member since 2008-09-03)

2

This is a lot tricker than you might think.
I did something awhile ago which took into account holidays and weekends etc. Your main issue is holidays. You’ll have to hard-code them into your report.

I created an array, one for each year to store the holidays such as:
local datevar array hols2007 := [CDate(2007,01,01),CDate(2007,01,26),CDate(2007,04,06),CDate(2007,04,09),CDate(2007,04,25),CDate(2007,06,11),CDate(2007,12,25),CDate(2007,12,26)];

local datevar array hols2008 := [CDate(2008,01,01),CDate(2008,01,28),CDate(2008,03,21),CDate(2008,03,24),CDate(2008,04,26),CDate(2008,06,09),CDate(2008,12,25),CDate(2008,12,26)];

local datevar array hols2009 := [CDate(2009,01,01),CDate(2009,01,26),CDate(2009,04,10),CDate(2009,04,13),CDate(2009,04,27),CDate(2009,06,08),CDate(2009,12,25),CDate(2009,12,28)];

I then got the current date and from there decided which year array to use for the public holidays like:

if Year(CurrentDate) = 2008 then
(numPublicHols := UBound(hols2008);
redim preserve hols[numPublicHols];
hols := hols2008;)
//etc

In my calculation I would then loop around. Part of my formula that might help was:

//public holidays variables
local numbervar i;
global numbervar numPublicHols;
global datevar array hols;

RunningDate := StartDate;
WHILE RunningDate in (StartDate to EndDate) do
(
i := 1;
for i := 1 to numPublicHols do
(
if RunningDate = hols[i] then
RunningDate := Date(DateAdd(“d”,1,RunningDate));
// i := 1; //go through the loop again as we’ve a new date to check
);
If NOT (DayOfWeek(RunningDate) IN [crSaturday,CrSunday]) THEN (

    TotalDayCount := TotalDayCount + 1;

    Weeknum := DateDiff("ww",StartDate,RunningDate,crSaturday)+1;

    select Weeknum
        case 1 : Week1DayCount := Week1DayCount + 1
        case 2 : Week2DayCount := Week2DayCount + 1
        case 3 : Week3DayCount := Week3DayCount + 1
        case 4 : Week4DayCount := Week4DayCount + 1
        case 5 : Week5DayCount := Week5DayCount + 1;
);

RunningDate := Date(DateAdd("d",1,RunningDate));

This is all probably way too much for what you need but might give you an idea that you’ll need to go back one day at a time and then see if that date is a weekend or a holiday (from the array) and decide to skip it or count it.

chesl73 (BOB member since 2008-05-15)