WorkDays Formua returning zero values

[Shanna]

Hi there,

I hve altered Ken's working days formula (thanks Ken!) to suit my report however instead of returning the absence days as required, I am getting zero vaules returned and I can't see why.. as usual, any help much appreciated, formula attached below:


numberVar TotalAbs;
DateVar Begin := { X | SW_0009a_Monthly_Abs_By_Dept.BEGIN_DT};
DateVar Return := { X | SW_0009a_Monthly_Abs_By_Dept.RETURN_DT};
numberVar AbWeeks1;
numberVar AbWeeks2;
numberVar AbWeeks3;
numberVar AbWeeks4;

numberVar AbDays1;
numberVar AbDays2;
numberVar AbDays3;
numberVar AbDays4;


//Calcs for each of 4 absence instances below:

numberVar DayReturn := Day( Return );
numberVar DayBegin := Day( Begin );

AbWeeks1:= Truncate( ( DayReturn - DayOfWeek( Return ) + 1- ( DayBegin - DayOfWeek( Begin ) + 1 ) ) / 7 ) * 5;
AbDays1 := DayofWeek( Return ) - DayOfWeek( Begin ) + 1; //adjusts first and last week depending on when in the wk you start & end

AbWeeks2:= Truncate( ( DayReturn - DayOfWeek( Return ) + 1 ) / 7 ) * 5;
AbDays2:= DayofWeek( Return ) - DayOfWeek( Begin ) + 1;

DateVar EndOfJan := Date( Year ( CurrentDateTime ), 01, 31 );
DateVar StartOfJan := Date( Year ( CurrentDateTime ), 01, 01 );

AbWeeks3:= Truncate( ( 31- DayOfWeek( EndOfJan ) + 1 - ( DayBegin - DayOfWeek( Begin ) + 1 ) ) / 7 ) * 5;
AbDays3:= DayOfWeek( EndOfJan ) - DayOfWeek( Begin ) +1;

AbWeeks4:= Truncate ( (31 - DayOfWeek( EndOfJan ) + 1 - ( 1- DayOfWeek( StartOfJan ) + 1 ) ) / 7 ) * 5;
AbDays4:= DayOfWeek( EndOfJan ) - DayOfWeek( StartOfJan ) +1;

numberVar HolsJan := 1; //hogmanay holiday
numberVar HolsDec:= 2; //xmas, boxing

//for absences in January in the current year

IF
Month(Begin)= 1 AND
Month(Return) = 1 AND
Year(Begin) = Year (CurrentDateTime) AND
Year(Return) = Year (CurrentDateTime) THEN
TotalAbs = AbWeeks1 + AbDays1;

//for absences ending in January this year which have started prior to Jan this year deducting 1 day for new Years Holiday ( therefore began outwith current year)

IF
Month(Return) = 1 AND
Year(Return) = Year (CurrentDateTime) AND
Year(Begin) < Year (CurrentDateTime) THEN
TotalAbs = AbWeeks2 + AbDays2 - HolsJan;

// Absences in Jan this year which carry into Feb/subsequent months this year

IF
Month(Begin) = 1 AND
Year(Begin) = Year (CurrentDateTime) AND
Year(Return) = Year (CurrentDateTime) AND
Month(Return) > 1 THEN
IF begin > Date (Year (CurrentDateTime),01,01 )
THEN
TotalAbs:= AbWeeks3+ AbDays3
ELSE
TotalAbs:= AbWeeks3+ AbDays3 - HolsJan

//Absences in Jan this year which start outwith Jan this year and finish outwith Jan this year

ELSE
IF Begin < Date (Year (CurrentDateTime),01,01 )
AND Year(Return) = Year(CurrentDateTime)
AND month(Return) >1
THEN
TotalAbs:= AbWeeks4 + AbDays4 - HolsJan;

 

[kenhamady]

What exactly are you trying to do? Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[Shanna]

Hi Ken, thanks for getting back. Okay...

This is what I'm trying to do:

The above formula was for absence in January. I trying to work out each instance of absence in each month (the formula above is for January only) for each employee. I am going to repeat the formula above and create seperate fromulas for every month of the year (if and when I ever get it working). I will have a JanAbs formula , a FebABs formula etc...

I was going to take historic yearly absence (i.e Jan 2000, Jan 1998 into consideration also as there are people on long term sick who have been off for years, but this is too complex as it would involve creating another 40 + formulas (JanCurrentYR, Jan1997, Jan1996 etc). Therefore I have limited it to current year only.

There are four absence instances that can occur in a month, these are:

1) Absence can start in January of the current year and end in January of the current year.

2) Absence can begin before January (say in Dec of the previous year) and end in January of the current year.

3) Absence can start before Jan and end after Jan (i.e Start Dec 00 end Feb 01)

4) Absence can start in Jan and end after Jan (i.e Feb)


I am trying to get to a point where the data looks like this:

EmplID Name JanAbs(workingDays) FebAbs ...... DecAbs

1 A.Person 2 0 0
2 B.Person 6 1 7
3. B.Person 2 4 3
3 C.Person 4 0 0

I need it like this to build in running totals.

I hope this is clear ?

Ta in advance,

Shan

 

[kenhamady]

Are you trying to use my formula for each month, and only include the dates in that month? In other words, If they wento out in December and came back in February, you want to count the days broken into 3 different months?

If so I wouldn't do the whole calculation 4 different times, I would calculate a start and end date and run them through each calculation once. I wouldn't even change my formula, except to print 0 when the start and end are BOTH outside of that month. I would create a separate JanStart and JanEnd formula (and another pair for each month) to make it easier to troubleshoot.

ie. for the JanStart date use:

If {Start.date} < Date(yyyy,1,1)
then Date (yyyy,1,1)
else {Start.date}

for JanEnd use:
If {End.date} > Date(yyyy,1,31)
then Date (yyyy,1,31)
else {End.date} Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/