Difference in hours for badging date and time activity

[Fermo Zolli]

Hello, everybody,

Tried many things but cannot get a good result.

I have created a group 1 with user ID and a group 2 with date.
Then in details I have all entries related as i.e.

08/12/19 05.43.59 IN
08/12/19 15.19.00 OUT

This user worked in first shift. All entries are sorted with a IN time and a OUT time. More problems come when the user is working on night shift as it enters one day and leaves the day after. Or when the user is going out for lunch and coming back, I get four entries. Sometimes a manitenance user is called at home and comes several times i.e. during the night, so the number of IN and OUT entries is increasing.

How can I calculate to total worked hours per day in a simple and sure way, please?
Is somebody able to give an help on this?

Kind regards, everybody,
Fermo

 

[SkipVought]

Hi,

Not a CR guy, but in principle...

Lapse time in days & fractions thereof = (OUT Date + Time) - (IN Date + Time)

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[lbass]

What is your business practice regarding hours that cross days? Do you count the hours towards in the in day or the out day? Or do you split the time and count some hours toward one day and some to another?

-LB

 

[Fermo Zolli]

Hi,

Sorry for late answer, ver busy this morning!
Our pracice is that the hours will be calculated in the day I start to work, so in the IN day.
IN and OUT are in a separated field, just in case I have to use it in the formula.
the total worked hours should appear in the group 2 header, the one for the date.

Thank you for any help and kind regards,
Fermo

 

[lbass]

Are you displaying the details in the report? If not, could you use the Group 2 footer for the results? Otherwise I think you’ll need to use a subreport to get the results in the GH2. After this, I can’t really respond until later today.

-LB

 

[Fermo Zolli]

Group footer would also be ok, no problem.

Thank you very much

 

[lbass]

Create these formulas:

//{@reset} to be placed in the date groupheader and suppressed:
whileprintingrecords;
numbervar minswkd := 0;
numbervar hrswkd := 0;

//{@hrswkd} to be placed in the detail section and suppressed after you confirm it is working as expected:
whileprintingrecords;
numbervar minswkd;
numbervar hrswkd;
if not onlastrecord and
{table.inout}="In" and
date(next({table.datetime})) in [date({table.datetime}),date({table.datetime})+1] then
minswkd :=minswkd + datediff("n",{table.datetime},next({table.datetime})); //in minutes
if (
onlastrecord and
{table.inout}="In"
) or
not(date(next({table.datetime})) in [date({table.datetime}),date({table.datetime})+1]) then
minswkd :=minswkd + datediff("n",{table.datetime},datetime(date({table.datetime})+1,time(0,0,0)));
hrswkd := minswkd/60

//{@results} to be placed in the date group footer:
whileprintingrecords;
numbervar hrswkd;

I set this up so that is for some reason there is no corresponding "Out" record or the "Out" record is later than the next day, the hours will count only up to midnight of the "In" day.

-LB

 

[Fermo Zolli]

Compliments LB! look really nice.

Only one problem.
Calculation is ok for all components inside group 1, meaning that all IN and OUT date are calculated regularly. @hrswkd will show the same amount in the raws of IN and OUT, as well as in @Result.
But on last IN and OUT couple of each group 1, the 2 numbers will be different, because last OUT raw is doing a kind of SUM, so @Result will get this higher number.
Example:
15/12/19 13:44:24 IN @hrswkd = 8.42 (correct)
15/12/19 22:09:08 OUT @hrswkd = 10.27 (not correct)
@Result = 10.27 (not correct)

Fermo

 

[lbass]

Sorry--change the second if statement so that the formula then looks like this:

whileprintingrecords;
numbervar minswkd;
numbervar hrswkd;
if not onlastrecord and
{@in-out}="In" and
date(next({table.datetime})) in [date({table.datetime}),date({table.datetime})+1] then
minswkd :=minswkd + datediff("n",{table.datetime},next({table.datetime})); //in minutes
if (
onlastrecord or
not(date(next({table.datetime})) in [date({table.datetime}),date({table.datetime})+1])
)
and
{@in-out}="In" then
minswkd :=minswkd + datediff("n",{table.datetime},datetime(date({table.datetime})+1,time(0,0,0)));
hrswkd := minswkd/60

-LB

 

[Fermo Zolli]

Thasnk you very much, LB ... and again compliments!

This works fine now.

My next questions now are:

Having detailed records as:
15/12/19 13:44:24 IN
15/12/19 22:09:08 OUT

1) How to create a formula telling me that this time interval corresponds to an afternoon shift (standard 14.00 - 22.00), giving me a result as "A"?
Same I would then do for all other intervals ( daily 08.00 - 17.00, morning 06.00 - 14.00, night 22.00 - 06.00)
2) How to recognise other small intervals as a couple of hours worked by a maintenance guy called from home during night or on Saturday or Sunday?

3) If it is a daily interval, for a clerk i.e., then I have too possibilities:
- the person went out for lunch break, so I have a IN-OUT record for the morning and another one for the afternoon: in this case everything ok.
- the person remains in the factory for having lunch in the internal cantine, so I have only one IN-OUT record for the all day, and I should substract 1 hour for his break.

Thasnk you for any help.
Fermo

 

[lbass]

Please show sample data for several days for someone who works the night shift using your current groups and showing the in/out field, too.

-LB

 

[Fermo Zolli]

Pleae find enclosed Excel file.

Thank you,
Fermo

 

[lbass]

I only worked with the first employee's data, but found that the in and out hours varied so greatly as did the totals hour worked, that the best I could do to label the shift was by checking the first time for that day and then requiring there to be at least 8 hours of work.

First I created a formula like this:
//{@indate}:
if {table.inout}="IN" then {table.datetime} else
date(9999,9,9)

//@Shift} to be placed in GF2:
whileprintingrecords;
numbervar hrswkd;
if time(minimum({@indate},{table.datetime})) in time(7,0,0) to time(9,0,0) and
hrswkd >= 8 then
"Daily" else
if time(minimum({@indate},{table.datetime})) in time(5,00,0) to time(7,0,0) and
hrswkd >= 8 then
"Morning" else
if time(minimum({@indate},{table.datetime})) in time(13,0,0) to time(15,0,0) and
hrswkd >= 8 then
"Afternoon" else
if time(minimum({@indate},{table.datetime})) in time(21,0,0) to time(23,0,0) and
hrswkd >= 8 then
"Night" else
"Other" //here are your other small intervals

You have some cases where there are two "ins" and no "outs", etc. Not sure what your policies are. You have to be able to distinguish overtime hours from work hours that might include lunch. You could try counting the number of in/out records per day and if there are only two and the employee exceeds your criterion for number of hours worked by at least one hour, you could deduct an hour, but you might have errors.

-LB

 

[Fermo Zolli]

LB, sorry, it took a while!

Everything ok, but every time the date is changing, it counts the hours double.

I also would like @Results to round up to entire or half numbers: how to do that?

Finally with this report I am trying to sort data from 2 different databases. The one for badging and the other one for maintenance management. What I would like to do is to compare and calculate the difference between the badged hours and the total hours registrated in the manitenance work orders.
Tried many things with direct connection and with a subreport, but CR is crashing (direct connction between 2 db tables) or not sorting the data with subreport. Do not understand, or most probably do not know how to manage this.
Another problem is due to the fact that the maintenance employee code is not equal to their badge number. So I had to use a free filed from maint. employee table to give the badge number and connect this number with the badging database. This is workking, but ehen I try to link other tables from maintenance database, using the employee code (not the badge code), no chance!
Any idea or suggestion?

I was successfull in sorting out and deducting the lucnh hour!

Is thre a way to repeat GH1 data on the next page, if its records are not fully shown in previous page? do not fin d a way for this.

Thank you very much for all your help. It gives me opportunity to grow my knowledge a lot.
In case you celebrate it, my best wishes for a Merry Christmas to you and familly.
Fermo