Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Difference in hours for badging date and time activity

Status
Not open for further replies.

Fermo Zolli

Technical User
Aug 20, 2019
10
IT
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
 
Hi,

Not a CR guy, but in principle...

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

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
AD1D550E-FCB5-4AB6-A3C8-1F86CACE2554_ne1bcz.jpg
 
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
 
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
 
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
 
Group footer would also be ok, no problem.

Thank you very much
 
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
 
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
 
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
 
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
 
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
 
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top