Working Days Calculation

[BradCustom]

I'm working with Crystal 2008 on a report were I'm trying to find the number of regular hours and overtime hours within a date range.
I've created a formula that works as long as the report's date range is either less than 7 days or if greater than 7 days, then it needs to be run in multiples of 7.

Below are three of the formulas, the first is to find the number of days.
@TotReportDays

DateDiff ("d",{@MinWorkDate} ,{@MaxWorkDate} )

@TotRegPayrollHrs

if ({@TotReportDays}) <= 7
then
(
if (((Sum ({@PayrollLineHrs}, {Employee.Employee})-Sum ({@EmpDayHolHrs}, {Employee.Employee}))-Sum ({@EmpDaySickHrs}, {Employee.Employee}))-Sum ({@EmpDayVacHrs}, {Employee.Employee}))>40
then 40
else (((Sum ({@PayrollLineHrs}, {Employee.Employee})-Sum ({@EmpDayHolHrs}, {Employee.Employee}))-Sum ({@EmpDaySickHrs}, {Employee.Employee}))-Sum ({@EmpDayVacHrs}, {Employee.Employee}))
)
else
(
if ({@TotReportDays}) > 7
then
(
if (((((({@TotReportDays})/7)*40)-Sum ({@EmpDayHolHrs}, {Employee.Employee}))-Sum ({@EmpDaySickHrs}, {Employee.Employee}))-Sum ({@EmpDayVacHrs}, {Employee.Employee})) > Sum ({@PayrollLineHrs}, {Employee.Employee})
then
Sum ({@PayrollLineHrs}, {Employee.Employee})
else
((((({@TotReportDays})/7)*40)-Sum ({@EmpDayHolHrs}, {Employee.Employee}))-Sum ({@EmpDaySickHrs}, {Employee.Employee}))-Sum ({@EmpDayVacHrs}, {Employee.Employee}))
)

@TotOTPayrollHrs

if ({@TotReportDays}) <= 7
then
(
if (((Sum ({@PayrollLineHrs}, {Employee.Employee})-Sum ({@EmpDayHolHrs}, {Employee.Employee}))-Sum ({@EmpDaySickHrs}, {Employee.Employee}))-Sum ({@EmpDayVacHrs}, {Employee.Employee}))>40
then (((Sum ({@PayrollLineHrs}, {Employee.Employee})-Sum ({@EmpDayHolHrs}, {Employee.Employee}))-Sum ({@EmpDaySickHrs}, {Employee.Employee}))-Sum ({@EmpDayVacHrs}, {Employee.Employee}))-40
else 0
)
else
(
if ({@TotReportDays}) > 7
then
(
if (((((({@TotReportDays})/7)*40)-Sum ({@EmpDayHolHrs}, {Employee.Employee}))-Sum ({@EmpDaySickHrs}, {Employee.Employee}))-Sum ({@EmpDayVacHrs}, {Employee.Employee})) < (((Sum ({@PayrollLineHrs}, {Employee.Employee})-Sum ({@EmpDayHolHrs}, {Employee.Employee}))-Sum ({@EmpDaySickHrs}, {Employee.Employee}))-Sum ({@EmpDayVacHrs}, {Employee.Employee}))
then
(((Sum ({@PayrollLineHrs}, {Employee.Employee})-Sum ({@EmpDayHolHrs}, {Employee.Employee}))-Sum ({@EmpDaySickHrs}, {Employee.Employee}))-Sum ({@EmpDayVacHrs}, {Employee.Employee})) - ((((({@TotReportDays})/7)*40)-Sum ({@EmpDayHolHrs}, {Employee.Employee}))-Sum ({@EmpDaySickHrs}, {Employee.Employee}))-Sum ({@EmpDayVacHrs}, {Employee.Employee}))
else 0
)

What I'd like is to beable to run this report for any number of days. Presently if the report's date range is greater than 7 but not a multiple of 7 then the Total Regular Hours and Total OT Hours caulation returns an incorrect value.

Thanks for your help!

 

[lbass]

Try this:

//{@TotRegHrs}:
numbervar x := Sum ({@PayrollLineHrs}, {Employee.Employee});
numbervar y := Sum ({@EmpDayHolHrs}, {Employee.Employee});
numbervar z := Sum ({@EmpDaySickHrs}, {Employee.Employee});
numbervar w := Sum ({@EmpDayVacHrs}, {Employee.Employee});
numbervar tot := {@TotReportDays};
numbervar wks := tot/7;
numbervar reg := wks*40;
if x-y-z-w > reg then
reg else
x-y-z-w

//{@TotOTPayrollHrs}:
numbervar x := Sum ({@PayrollLineHrs}, {Employee.Employee});
numbervar y := Sum ({@EmpDayHolHrs}, {Employee.Employee});
numbervar z := Sum ({@EmpDaySickHrs}, {Employee.Employee});
numbervar w := Sum ({@EmpDayVacHrs}, {Employee.Employee});
numbervar tot := {@TotReportDays};
numbervar wks := tot/7;
numbervar reg := wks*40;
if reg < x-y-z-w then
x-y-z-w-reg else
0

-LB

 

[BradCustom]

LB
Unfortunately your formula has the same result as mine. I'm wondering if it's because a "Work Week" hasn't been defined?
Here's the exact situation when the calculation doesn't work.

If the report date range is from Monday (Feb 6) to Friday (Feb 17) or any Friday greater than the 17th. The number of days is not evenly divisible by 7 so the @TotOTPayrollHrs is always 0 and the @TotRegHrs is equal to the total hours worked Regular + Overtime.

Thanks for your help!

 

[lbass]

How are you forcing the removal of decimals? My formulas aren't requiring that--and I'm assuming you are prorating the workweek for this purpose.

Did you use my exact formulas when testing?

-LB

 

[BradCustom]

LB
The two fields @TotRegPayrollHrs and @TotOTPayrollHrs are formated to display 2 decimal places.
I know there isn't a provision in your formula as there isn't one in mine to define what a week is, I was thinking that maybe it's needed? Yes, your right about prorating the week.

I did use your formula exactly as you wrote it when I tested it.

Right now the formula works fine as long as it's run for multiples of 7 days or something less than 7 days. Unfortunately not every Month begins and ends on the same day of the week.

What if in the @TotReportDays formula I defined how many workdays there are between the MinWorkDate and MaxWorkDate?

Thanks for your help!

 

[BradCustom]

I've come up with a solution that will work for this problem.

I've created a formula called @WorkDaysBetweenMinMax

WhileReadingRecords;
Local DateVar Start := Date({@MinWorkDate});   
Local DateVar End := Date({@MaxWorkDate}); 
Local NumberVar Weeks; 
Local NumberVar Days; 

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);   

Weeks + Days

This tells me how many working days there are between {@MinWorkDate} and {@MaxWorkDate}. I can then multiple that result times 8 hours per day to obtain the total regular hours for that date range. Anything greater than that number is overtime hours.

I'll let you know how it works when I've finished changing the formulas.

Thanks for your help!

 

[BradCustom]

Below is how I resolved the problem I was having with {@TotRegPayrollHrs} and {@TotOTPayrollHrs} formulas.

As I stated in the previous post I created the formula {@WorkDaysBetweenMinMax} which tells me the number of working days in the selected date range.

Then I modified LB's formulas as shown below and I can now run the report for any date range and the value for {@TotRegPayrollHrs} and {@TotOTPayrollHrs} is correct.

@TotRegPayrollHrs

numbervar x := Sum ({@PayrollLineHrs}, {Employee.Employee});
numbervar y := Sum ({@EmpDayHolHrs}, {Employee.Employee});
numbervar z := Sum ({@EmpDaySickHrs}, {Employee.Employee});
numbervar w := Sum ({@EmpDayVacHrs}, {Employee.Employee});
numbervar tot := {@WorkDaysBetweenMinMax};
numbervar wks := tot/5;
numbervar reg := wks*40;
if wks  < 1
then x-y-z-w
else
(
if x-y-z-w > reg then
reg 
)

@TotOTPayrollHrs

numbervar x := Sum ({@PayrollLineHrs}, {Employee.Employee});
numbervar y := Sum ({@EmpDayHolHrs}, {Employee.Employee});
numbervar z := Sum ({@EmpDaySickHrs}, {Employee.Employee});
numbervar w := Sum ({@EmpDayVacHrs}, {Employee.Employee});
numbervar tot := {@WorkDaysBetweenMinMax};
numbervar wks := tot/5;
numbervar reg := wks*40;
if wks  > 1
then x-y-z-w-reg
else 0

Thanks for all the help!!

 

[lbass]

Okay, glad you got this working. I was wondering about that formula {@TotalReportDays}, and I guess that was the issue.

-LB