• 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.

Date and Time Look Up Formula

L

lkf2007

New Member
I manage a group of exempt level supervisors who do not punch a time clock. Because of time abuse (frequently coming in late, leaving early, etc) HR created an attendance gate for supervisor bonuses. Because they do not punch a time clock the only method I have for validating they were on time is to review badge swipes in and out of the building. As you might imagine this is a very tedious process for me and can take a few hours at the end of each month to review. I'm looking for a way to automate the process or make it easier for myself.


The badge swipe report comes to me in excel format. Currently I review the first and last swipe for that particular day to see if it matches with the employees schedule. I highlight any instances in which the employee came in late or left early by more than ten minutes. The date on my spreadsheet is currently in this format: 12/16/2012 23:10


Does anyone have any suggestions or formulas I could use to test the value in this column so that I don't have to manually look at each line item?


Thanks!

Lisa
 
Hi Lisa,


Welcome to the forums!! Can you post a sample work book with your data??


Regards,
 
Hi, lkf2007!

While I give a look to your uploaded file, would you consider this as an alternative?

http://www.technovelgy.com/ct/Science-Fiction-News.asp?NewsNum=2357

Regards!
 
Hi, lkf2007!

Having gave a look at your uploaded file, I have several questions:

a) do you receive a report file for each supervisor (in your example file, 11750 Jane Doe), a report for all supervisors, or a report for all employees?

b) I don't fully understand which controls or operations you perform with this data, specifically what do you mean with "review the first and last swipe for that particular day to see if it matches with the employees schedule", would you please elaborate a bit more and write down examples for a few days regarding the uploaded file?

c) are there additional report files with supervisor schedules? if yes, of what format?

Regards!
 
Hi Lisa


Please see this:


http://dl.dropbox.com/u/60644346/Example%20Badge%20Swipe%20Report%20Dec%202012.xlsx


You can request SirJB7 for a macro he can make things simpler for your :)


@ SirJb7 & Lisa: Its 1:45 AM Good Night!! :p


Faseeh
 
HAHA! SirJD7 - That sounds like a brilliant solution! I don’t know if my employees would be willing to comply though :p
 
@Faseeh

Hi!

Thanks for telling people that they could ask me to do things for them... I'm almost near to fall in love with you... and you know yet, from love to hate there's just a single and short step... :p

Regards!

PS: BTW, I'm at 5:45PM (GMT-3).


Hi, lkf2007!

Sorry for the assumption, I thought you've already had one of these:

http://daninjectdartguns.com/

Regards!

PS: After your daily shooting practice, would you please answer my earlier doubts?
 
Hi Lisa ,


Can you download the following file , and see if it helps ?


http://speedy.sh/dP9W9/Example-Badge-Swipe-Report-Dec-2012.xlsx


Narayan
 
Faseeh – Thank you for your reply. If I am manipulating this worksheet correctly you would have to enter each day of the month and the corresponding “seq id” at the top of the sheet for the formula to work, correct? It may not save much time since I would have to enter each day of the month.


Narayank991 – Thank you also. This format seems to work better. One request, is it possible to add a format or formula that will compare the “earliest swipe in” with their scheduled start time and automatically highlight any instances where they are more than 10 mins late? And the same for “latest swipe out” where it would highlight if they left more than 10 mins early?


Thanks!

Lisa
 
Hi Lisa ,


I am not able to understand your schedule file ( Sup Rotation Sample.xlsx ) ; can you make a few entries in the file I uploaded , and upoad the resulting file , so that I can get an idea of how to correlate the swipe in and swipe out entries with the schedule.


I don't understand where the teams come in , since the name is the same throughout ; will there be one schedule file for each person ?


Narayan
 
I've modified the schedule spreadsheet so that it only includes the relevant information for the badge swipe process. let me know if you have any questions.


https://hotfile.com/dl/188214140/58c6306/Sup_Rotation_Sample.xlsx.html
 
Hi, lkf2007!

What a coincidence! I didn't forget about your topic and yesterday I changed (added a new worksheet so as to not change yours) the schedule to be easily handled... give a look at it:

https://dl.dropbox.com/u/60558749/Date%20and%20Time%20Look%20Up%20Formula%20-%20Sup%20Rotation%20Sample%20%28for%20lkf2007%20at%20chandoo.org%29.xlsx

So you can choose to keep the older schema and use the added worksheet for processing (automatically I hope, if anyone -including me- provides the proper solution), or simply keep your newly created worksheet. Please tell us about your preferences so as to keep on working from that.

Regards!

PS: My greetings to Agnes Hampton :p
 
Hi Lisa ,


Thanks for posting a revised version of your schedule file , since this appears to be more formula-friendly !


My questions are :


1. In your earlier swipe data file , you had used the name Jane Doe ; in your schedule file , you have Supervisor 1 , Supervisor 2 and so on ; I assume that one of these names will be Jane Doe , so that we can lookup the name from the swipe data file into this schedule file. Can you please confirm this ?


2. Where the schedule file has an IN time and an OUT time on any day of the week , things are simple ; what is supposed to happen if the schedule file has an OFF on a day where the swipe data file has IN times and OUT times ?


Narayan
 
1) Yes, you can assume that one of those names would be Jane Doe


2) If they are off, and have swipes for that day perhaps we can set the formula to return the word "Unscheduled"? If they were not scheduled for that day I am not concerned with what time they were in the building.


Thanks!

Lisa
 
I noticed I forgot to delete her name after I posted it! Durh... so much for my attempts to protect privacy!
 
Hi, lkf2007!

Don't worry so much about that, Lisa, aka lwallace1! :)

If actually highly concerned about privacy don't forget to remove personal information (flap Details from Properties in the Windows Explorer) before uploading a file.

BTW, could you solve you issue or still pending? If not, which version of the new schedule you want to keep, mine from the original file or your new file? I think I've asked this before (here, http://chandoo.org/forums/topic/date-and-time-look-up-formula#post-69221) and you didn't answer, maybe you're very busy aiming at silhouettes of your elusive supervisors.

Regards!
 
Hi Lisa ,


I have made a first version ; please go through it and comment.


http://speedy.sh/bwDJH/Example-Badge-Swipe-Report-Dec-2012.xlsx


The first problem I can see is how to decide whether a time entry is a swipe in or a swipe out e.g. consider the following 3 entries :

[pre]
Code: 
12/1/2012 21:25
12/2/2012  0:14
12/2/2012 12:49
[/pre]
How do we decide where the middle entry fits in ?


If we go strictly by the date , then this is the first entry on 12/2/2012 ; but if we see the entries in proximity , then the earlier entry ( 12/1/2012 21:25 ) is nearer to it than the next entry ( 12/2/2012 12:49 ) ; do we conclude that this entry is the last swipe out for a swipe in on 12/1/2012 ?


This will then make 12/2/2012 12:49 the first swipe in for 12/2/2012.


Narayan
 
Is there a way that we can count anything between 00:01 - 4:00 as an out punch for the previous day?


In the case of these three punches,

12/1/2012 21:25

12/2/2012 0:14

12/2/2012 12:49

The middle one of 0:14 would be the "out punch" for the 1st. The last entry would be in true "in punch" for the 2nd.
 
I'm having a little difficultly folloiwng the changes. Which column would tell me if they were late or early? Thanks!
 
You must log in or register to reply here.
Back
Top