• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Break Time calculation( in- out)

D

Dinu Kunjunju

New Member
Folks

i have a swipe report in excel and i want to calculate the break time for each person. i tried to fix this through referring our blogs but i am not able to fix it. somebody please help me to fix this issue.
 

Attachments

  • Swipe Report.xls
    343 KB · Views: 9
Hello Narayan

Thanks for the update. its really help full. I would check one thing with you as I need to exclude the first entry and last exit from a date for a particular date and the rest of the entry exit will calculate the break time. I am seeing few persons are taking 2 more hours I think bcz of this.
 
Dinu Kunjunju said:
Hello Narayan

Thanks for the update. its really help full. I would check one thing with you as I need to exclude the first entry and last exit from a date for a particular date and the rest of the entry exit will calculate the break time. I am seeing few persons are taking 2 more hours I think bcz of this.
Click to expand...
Hi ,

Can you point out one or two specific cases so that I can check it out ?

Narayan
 
Hello Narayan

A Safe example is Please select Card holder as T K, Dinu and select date 12th of March you can see the first time is 11.0.10 Am actually this was he entered in office first time that day. so this should be excluded from break time calculation. then 8.26.54 PM he is left from office and his shift is finished so this time also excluded rest exit and entry will be added into the break time. hope this make sense.
 
Dinu Kunjunju said:
Hello Narayan

A Safe example is Please select Card holder as T K, Dinu and select date 12th of March you can see the first time is 11.0.10 Am actually this was he entered in office first time that day. so this should be excluded from break time calculation. then 8.26.54 PM he is left from office and his shift is finished so this time also excluded rest exit and entry will be added into the break time. hope this make sense.
Click to expand...
Hi ,

Will this apply to all cardholders ?

Will it apply for every date ?

Suppose a cardholder has only one entry and one exit on any particular date ; will both of these be excluded ?

Narayan
 
Dinu Kunjunju
Here is one sample for You.
You can see daily 'break times'
and comments shows ALL Entry & Exit times.
If they miss to ENTRY or EXIT -
then there would be challenge or
if they work night shift.
Press [Do It] to see results.
 

Attachments

  • Swipe Report.xlsb
    75.6 KB · Views: 6
Dinu Kunjunju
I added few features, cause eg #5 Reply.
[Calc Breaks] do as name says
[Show Name] You can select any name after [Calc Breaks] to see its all rows
... back to 'normal view' with ...
[All Rows] shows all rows
[ReSet Hides] as there are 'sometimes extra row'
which You have marked as 'hide' and
if You would like to 'forget' ALL of those, press [ReSet Hides].
'Hiding':
Highlight any row Device-cell by any 'Fill Color'.
After that row won't notice while NEXT [Calc Breaks].
That 'mark' will show by yellow 'Fill Color'-cell in results.
 

Attachments

  • Swipe Report.xlsb
    103.6 KB · Views: 7
NARAYANK991 said:
Hi ,

Will this apply to all cardholders ?

Will it apply for every date ?

Suppose a cardholder has only one entry and one exit on any particular date ; will both of these be excluded ?

Narayan
Click to expand...
Hi Narayan

Yes this for all date, if one person have single log in and log out you can exclude them bcz they are security or other staff not the employees reporting to me. from this report I will sort out the persons who reporting to me.

Regards,

Dinu Kunjunju
 
Dinu Kunjunju said:
Hi Narayan

Yes this for all date, if one person have single log in and log out you can exclude them bcz they are security or other staff not the employees reporting to me. from this report I will sort out the persons who reporting to me.

Regards,

Dinu Kunjunju
Click to expand...
Hi ,

Since vletm is also involved in giving you a macro solution , do you still want a formula-based solution ?

Narayan
 
Hi Narayn

Yes, I saw that one but I am not sure all exit and entry calculated apart from first log in and last log out. bcz I noticed that few of my staff are only take 25 minutes as per the report that's wrong. that's what I confused.
 
Dinu Kunjunju You wrote:
but I am not sure all exit and entry calculated apart from first log in and last log out. bcz I noticed that few of my staff are only take 25 minutes as per the report that's wrong. that's what I confused.
Did You do ... [Show Name] You can select any name after ...?
Did You checked those BreakTime-comments? There should be ALL logs!
You can SEE all that names rows from Your data!
This only tries to show what do You data have.
 
vletm said:
Dinu Kunjunju You wrote:
but I am not sure all exit and entry calculated apart from first log in and last log out. bcz I noticed that few of my staff are only take 25 minutes as per the report that's wrong. that's what I confused.
Did You do ... [Show Name] You can select any name after ...?
Did You checked those BreakTime-comments? There should be ALL logs!
You can SEE all that names rows from Your data!
This only tries to show what do You data have.
Click to expand...
Hello

Thanks for the detailed explanation, for a safe example after running the macro the user Antony Dinil got break time 3 minutes on 16th March 2018, I calculated it manually and the actual break time is around 1 hour. this is what my concern with it.
 
Dinu Kunjunju
Okay ... good note!
I checked and I found typo ... it show only the last 'Break'
16-Mar-2016 - Antony Dinil
Entry - Exit times: 6:28:40
Exit - Entry times: 2:20:57 Total: 8:49:37
Do those values 'look better'?

... I don't know Your around 1 hour ...
... and It's possible to get both ... times as shown above.

Ps. It's always wise to check some values manually!
 

Attachments

  • Swipe Report.xlsb
    107.3 KB · Views: 6
vletm said:
Dinu Kunjunju
Okay ... good note!
I checked and I found typo ... it show only the last 'Break'
16-Mar-2016 - Antony Dinil
Entry - Exit times: 6:28:40
Exit - Entry times: 2:20:57 Total: 8:49:37
Do those values 'look better'?

... I don't know Your around 1 hour ...
... and It's possible to get both ... times as shown above.

Ps. It's always wise to check some values manually!
Click to expand...

Hello

I have calculated manually the break time for Antony Dinil and I am getting 2.20.57 on 16th March. here is the way I calculated the break time. I consider the exit and entry marked in red.

09:17:00 PMKOCHI CARNIVAL 1F INVISTA EXITAccess Granted30115822104387ANTONY, DINIL
07:1:06 PMKOCHI CARNIVAL 1F INVISTA ENTRYAccess Granted30115822104387ANTONY, DINIL01:16:54
05:44:12 PMKOCHI CARNIVAL 1F INVISTA EXITAccess Granted30115822104387ANTONY, DINIL
04:4:51 PMKOCHI CARNIVAL 1F INVISTA ENTRYAccess Granted30115822104387ANTONY, DINIL00:09:14
03:55:37 PMKOCHI CARNIVAL 1F INVISTA EXITAccess Granted30115822104387ANTONY, DINIL
02:49:49 PMKOCHI CARNIVAL 1F INVISTA ENTRYAccess Granted30115822104387ANTONY, DINIL00:51:25
01:58:24 PMKOCHI CARNIVAL 1F INVISTA EXITAccess Granted30115822104387ANTONY, DINIL
12:45:12 PMKOCHI CARNIVAL 1F INVISTA ENTRYAccess Granted30115822104387ANTONY, DINIL
12:41:48 PMKOCHI CARNIVAL 1F INVISTA EXITAccess Granted30115822104387ANTONY, DINIL00:03:24
12:27:23 PMKOCHI CARNIVAL 1F INVISTA ENTRYAccess Granted30115822104387ANTONY, DINIL
02:20:57
 

Attachments

  • test.xlsx
    9.4 KB · Views: 5
Dinu Kunjunju
As I wrote .. verified my Break Time is 2:20:57
And it seems to be same as Your the latest Break Time value!
So, my version gives same values as You're looking for - or how?
 
vletm said:
Dinu Kunjunju
As I wrote .. verified my Break Time is 2:20:57
And it seems to be same as Your the latest Break Time value!
So, my version gives same values as You're looking for - or how?
Click to expand...
Yes, but what I am confused here is the total break time for Dinil 2.20 is only for one day not entire day. in your file each breaks are showing under different dates. correct me if I am wrong.
 
Dinu Kunjunju
That my previous sent file (#14) has output like (#18).
Now uploaded file has those Totals too.
I don't know which file ... which data You're writing.
I've used all the time data from Your (#1) the 1st message.
 

Attachments

  • Swipe Report.xlsb
    109.2 KB · Views: 8
vletm said:
Dinu Kunjunju
That my previous sent file (#14) has output like (#18).
Now uploaded file has those Totals too.
I don't know which file ... which data You're writing.
I've used all the time data from Your (#1) the 1st message.
Click to expand...

Thank you so much it is working fine. if I am getting new file then can I delete the existing data and replace it that will work right.
 
vletm said:
Dinu Kunjunju
I added few features, cause eg #5 Reply.
[Calc Breaks] do as name says
[Show Name] You can select any name after [Calc Breaks] to see its all rows
... back to 'normal view' with ...
[All Rows] shows all rows
[ReSet Hides] as there are 'sometimes extra row'
which You have marked as 'hide' and
if You would like to 'forget' ALL of those, press [ReSet Hides].
'Hiding':
Highlight any row Device-cell by any 'Fill Color'.
After that row won't notice while NEXT [Calc Breaks].
That 'mark' will show by yellow 'Fill Color'-cell in results.
Click to expand...
Hello Can you please help me with one question. I need one more feature is added to be this macro file. i have to show the Total Working hours along with the break time. that would be calculated from the first log in and last log out. then only i can analyze the exact break timings. we have total 9.30 working hours and i gave 1.30 breaking hours to staff. i want check if any one is logging early then then can adjust with breaks.
 
You must log in or register to reply here.
Back
Top