calculating overtime hours

building reports crystal reports
1

I have a requirement that needs to generate the number of overtime hours is an employee goes past 40 hours in a week if their work type is not PTO.

Here’s a formula that generates the hours:

Local NumberVar nHours :=0;
Local NumberVar nMinutes :=0;
Local StringVar sHours;
Local StringVar sMinutes;

convert the numbers to a time calculation:

if Sum({TotalTime}, {Employee}) > 59
then
(nHours := Truncate(sum({Total Time},{Employee}) / 60;
nMinutes := Remainder(sum({Total Time},{Employee}), 60)
else
nMinutes := sum({Total Time},{Employee});

format the layout for the report:
if nHours > 0
then
if nHours < 10
then sHours := “0” + ToText(nHours, 0)
else
sHours := ToText(nHours, 0);

if nMinutes >0
then
if nMinutes < 10
then sMinutes := “0” + ToText(nMinutes, 0)
else
sMinutes := ToText(nMinutes, 0)
else
sMinutes := “00”;

My question is what can I do to generate the OT? Say we have someone who works 42:30 in a week, I want to show the 2:30.

Any advice is appreciated. Thanks in advance.

ngosz1 :us: (BOB member since 2010-12-03)

2

Here’s what I would do:

  1. Take the “Local” off of your variables. This way they can be used in other formulas. Also take out the code that converts the time to a string.

  2. Create a new formula that will just look for PTO and provide a timestamp that can be summed.

  3. Create a third formula that looks something like this (I haven’t tested this!):

NumberVar nPTOHours;
NumberVar nPTOMin;
NumberVar nHours;
NumberVar nMinutes;
NumberVar nOTHours := 0;
NumberVar nOTMin := 0;
//Subtract the PTO time
if Sum({PTOTime}, {Employee}) > 0 then
(
nPTOHours := Truncate(sum({PTOTime},{Employee}) / 60;
nPTOMinutes := Remainder(sum({PTOTime},{Employee}), 60)
nHours := nHours - nPTOHours;
nMinutes := nMinutes - nPTOMin;
if nMinutes < 0 then
(
nMinutes := 60 + nMinutes; //nMinutes is negative, so we add it
nHours := nHours - 1;
)
)

if nHours > 0 then
(
nOTHours := nHours - 40;
nHours := 40;
nOTMin := nMinutes;
nMinutes := 0;
)

This formula will just do the calculations…

  1. Create a formula to display regular time:

NumberVar nHours;
NumberVar nMinutes;

ToText(nHours, 0, “”) + “:” + ToText(nMinutes, 0, “”)

  1. Use the same technique to create a formula that will display overtime.

-Dell

hilfy :us: (BOB member since 2007-04-16)

3

Thanks for your help!

Question - am I missing a semicolon?

Between the nPTOHours and nPTOMinutes calculation Crystal keeps toggling back and for about a missing right parenthesis.

nPTOHours := Truncate(sum({@PTOHours},{Employee}) / 60;
nPTOMinutes := Remainder(sum({@PTOHours},{Employee}), 60)

It claims missing parenthesis at the semicolon in nPTOHours. I add after the 60, and then the error moves to the variable name nPTOMinutes.

ngosz1 :us: (BOB member since 2010-12-03)

4

Yes, it’s missing a semi-colon. Sorry about that!

-Dell

hilfy :us: (BOB member since 2007-04-16)

5

Ok - that’s what I thought. :slight_smile:

The next part is this code acts like it’s not part of the formula

if nHours > 0 then
(
nOTHours := nHours - 40;
nHours := 40;
nOTMin := nMinutes;
nMinutes := 0;
)

Do I need parenthesis surrounding the whole formula?

Thanks again for your help!

ngosz1 :us: (BOB member since 2010-12-03)

6

What do you mean by “acts like it’s not part of the formula”? This is the part that calculates the actual overtime vs. regular time. It shouldn’t need any additional parentheses.

-Dell

hilfy :us: (BOB member since 2007-04-16)

7

It didn’t. I needed to add semicolons at the end of each function in the formula.

I think it’s just a matter of formatting now.

Thanks again!

ngosz1 :us: (BOB member since 2010-12-03)

8

I’ve run into a couple things with this solution:

when I have the nHours := 40, what if the hours is less than 40, say in a Group Footer?

And I can’t seem to get the OT hours to display in a new cell. All I continue to get is a 0:0 value. Do I have to place the whole formula in the new format cell after I assign the variable names?

Thanks

ngosz1 :us: (BOB member since 2010-12-03)