Employee Time Sheet (Beginner's Version)

[vipul.dedhia66]

Hello. My name is Vipul Dedhia & I live in Mumbai, India.

I am a Tax Consultant & as such, run a small-time office.

I wish to prepare a simple Employee Time Sheet as attached herewith. Allow me to summarize my thoughts.

Office timings are from 9:30 AM to 6:30 PM with 30 Minutes Lunch Break anywhere between 1:00 PM to 2:00 PM depending upon the employee's convenience. I wish to prepare such an Excel Sheet where the employee only needs to click on Start Button & Stop Button for recording his/her time. Example - Employee clicks start at 9:30 AM and stop at 1:00 PM for lunch & again start at 1:30 PM after lunch and stop at 6:30 PM. As simple as that. In last column, the total hours/minutes worked by the employee for the day should be automatically filled up with the help of some formula (I don't know that formula). Lastly, every employee gets separate Excel Sheet for himself/herself, they are the ones who will click start/stop & they should be able to see their timely attendance whenever they want. The only restriction I wish to put is that editing cannot be done for the time already gone. Example, today is 30-09-2017 so the employee should not be able to tamper with the earlier days, say 29-09-2017 (Meaning to freeze the earlier dates).

All the above is my own idea which I could think of. Please do let me know if there is a better way to record time of my employees. Also, I want to keep it as simple as possible, that is, no idle time and other complicated stuff. I also give 2 15-Minute tea/coffee breaks to my employees in the morning & evening but I do not wish to be that stringent. Just in-out time & lunch in-out time is sufficient for me. Also, I pay salary to my employees every half-month so I wish to keep it that way & not go for monthly schedule.

I am requesting the community to help me in my endeavours. Thank you.

P.S. I just remembered that you can automatically have the date and days of the month with formulas. Those formulas would be great to know if someone tells me about it.

 

[vletm]

vipul.dedhia66
Could You think something like this?
Basic version: Every employee has 'own button' which they use:

Employee starts to work > click own name > (green) and
then stops to work > click own name (white).
This tracks those clicks and it's possible to get working hours out.

Or layout could have more features like:

 

[vipul.dedhia66]

@vletm Thank you very much for taking some of your precious time out for helping me.

I liked the basic version very much. Its very simple to use. I feel like 2 more things should be added. First, instead of only 1 button, I think 2 buttons for each employee would be better, i.e, first one for in time in the morning and out time for lunch & second one for in time after lunch and out time in the evening. The reason I am doing this is because most of my employees stay near my office so I allow them to go home and have lunch & sometimes, they do not come back after lunch. Second, it should be 15 day schedule. I am attaching an Excel sheet to represent my thoughts which includes 2 sheets (One for Buttons & Other for Report - Need formula in report sheet for automatic calculation of hours and minutes).

One important thing as I have mentioned in my earlier post, employees must not be able to alter the earlier period's data (both buttons and report must freeze for the earlier period). Once again, I urge the community to help me in my endeavours. Thank you.

 

[vletm]

vipul.dedhia66 ... hmm?
Nice to know that You liked ... but
I offer one button per employee and those buttons will work forever!
No need to use new file after every 15 days (or 1-15 / 16- ..hmm.. 28..31).
Why needs two buttons per employee?
Why needs 30 buttons per employee?
You asked something simply to use or what?

As I wrote:
Employee starts to work > press own button (green) > makes timestamp
Employee stops to work > press own button (white) > makes timestamp
One employee need to find own button and
employee need to use only that button.
Even that would be 'challenge to use' ...
but there have to be at least one button per employee!

If there would be more than one button for each employee and/or
if there would be those buttons for each day
- that would be 'mission impossible' ... mess!

If wants more buttons for causes then something like below

but this would be one employee per file (cause texts can modify).

So far, I need to know 'input-layout' ...
after that I would ask more details about rules and output.

 

[vipul.dedhia66]

@vletm Once again, let me express my deepest gratitude for helping me out. THANK YOU.

Sorry, I complicated it for you. As far as 'input-layout' goes, then the Basic Version in your earlier post is perfect for my needs (20 nameplates are more than sufficient for my future needs as well). Lastly, YES, I am actually going to use it in my office. I won't waste the community's time for experimental purposes.

By the way, I don't know whether you are aware of it or not but there is an error in the Basic Version Excel. For example - If you click on Name1, then it turns green but it won't turn White if I can click on it again consecutively, that is, without clicking on any other Button. I must click on some other Button before clicking on the same button again. I am saying this because sometimes, during peak days, I keep my office open on Sunday as well & only 1 employee comes on that day. In which case, that employee won't be able to click on his/her tile again. Please let me know if I am wrong. Once again, thank you for your help.

 

[vletm]

Not complicated for me -
I just tried to write that Your version would be more complicate to use for You!

You wrote that You'll use it ...
Would that mean like:
'Emp1' comes to work - You will click 'Emp1'-btn
'Emp3' comes to work - You will click 'Emp3'-btn
'Emp1 goes out - You will click 'Emp1'-btn ... and so on.
Only some Sundays, only 'EmpN' will click 'EmpSelf' - IN and OUT?
Or how to use this?

That You named 'feature' isn't 'error' ...
it's 'feature' in that sample file and
it would change - not challenge.
This sample isn't for single user!

All depends what needs and how to use!

You wrote about 'Office Timings', okay!
How about 'Offices' working hours?

There are many kind of rules with 'working hours'!
Are daily working hours from 09:30 to 18:30
Do that include 30min Lunch Break or not?

The 'click' makes timestamp.
If 'clicker' misses few minutes or totally missed - that won't be nice!
There are NO manual fixing.
If missing 'IN' then there will be only 'OUT' (ex OUT 12:30) or
If missing 'OUT then there will be only 'IN' (ex IN 09:45).
Is there any rules?
If Lunch Break no match between 13-14, rules?
If no Lunch Break at all, rules?
If someone comes before 09:30, that would be okay ...
but 'working hours' starts 09:30 or how?
And same with evening, if leaves after 18:30?
'working hours' would count until 18:30 or how?

Those are some of basic things which have to know!
... and those are not complicate things!

 

[vletm]

vipul.dedhia66
... next sample version
without rules!

 

[vipul.dedhia66]

@vletm Once again, let me express my gratitude. I am feeling kind of obliged to you now. THANK YOU.

Firstly, our office has a small server, that is to say, all of our computers are connected by LAN. We have a spare laptop which is exactly where the excel file will be. The laptop will be powered on & each employee who comes in or goes out would click on his/her button in the excel sheet. I am a mere observer. I have no role to play except viewing the reports.

Office timings are 9:30 am to 18:30 pm which includes the lunch break of 30 min. But, the employee should be able to timestamp before 9:30 am as well as after 18:30 pm as sometimes, during peak days, it may be entirely possible to work till late in the office. I also totally get that if the employee misses or delays to click on the button, he/she would land in trouble. I wish to keep it that way so they become disciplined, which itself is a good habit to inculcate. If someone misses to timestamp in then he should not be able to timestamp out, that is, if there is no in then there is no out as well. One more thing I was thinking of implementing is that, when I used your Basic Version (2nd version), I by mistake clicked on the same button twice. This should not happen. I mean to say let there be a rule that there should a minimum interval between clicking on the same button consecutively. Example - If I click on Button 1 at 9:30 am then I should not be able to click on Button 1 again till 9:45 am, that is to say, 15 min gap before clicking on same button again which sounds logical as well as prudent to me. One other thing, when someone clicks on the button, there should be a written note appearing on screen that you have successfully logged in & similarly at the time of out, you have successfully logged out. This will relieve the employees that yes, they have successfully entered their attendance in the system.

Now, as far as the report goes, I am thinking of keeping it very simple. Emp1 worked for 'X' hours out of 8.5 hours on 'Y' day & total hours worked for the 15 day schedule works out to be 'A' hours out of 'B' hours. Simple. I hope I am not overbearing on you. Sorry for the trouble & thank you for helping.

 

[vletm]

hmm ...
1st and 2nd chapter - clear!

But 3rd chapter ...
You didn't answer to my questions ...
Office timings are Office timings, but ...
those 'working hours' could have many rules, depends working place and so!!
There no challenge to do timestamp before 09:30 or 18:30.

If someone 'timestamps' at 05:00 and 13:15 and 14:45 and 19:00 ...
( comes 'early' ... has loooong lunch ... forget to leave job at 18:30 )
What would be 'working hours' in Your office?
For me: from 09:30 to 13:15 and from 14:45 to 18:30
or what would rules say there?
or other cases which I tried to ask?

All rules should write to code! ...
and everything goes as dream after everyone follow those rules!

... and You wrote that there would be two parts per day
... if comes back to work after lunch (if not then there would be only one part)
if there are more than one part before and after lunch, what do rules say?
If employee misses 'IN-timestamp' or 'OUT-timestamp', ideas?
Ex yesterday employee has made 'IN-timestamp'... but for some good or unknown reason misses 'OUT-timestamp'. How to calculate 'working hours'?
or as next ...
That 15min 'gap' between 'timestamps' ... hmm ... it is possible, but but
then employee have to wait always that 15min before employee can make next 'timestamp' ... there will be cases that not good!
... if really have to leave before 15min then .. would that mean 'just 14min working hours ... or ... that could also be until 18:30 or later?

There can make 'fixing-procedure' for missed 'timestamps'.

a written note appearing on screen that you have successfully logged in & similarly at the time of out, you have successfully logged out.
Now, there has been a comment with 'employee button' which tells same information ... but of course that could be more visible!

Term 'very simple' should mean 'possible to do'!
For users, these should try to make as easy to use as possible ...
but other side of that would mean challenges with coding.

I can so some modifications,
but this needs those Your local rules!

 

[vipul.dedhia66]

@vletm Once again, THANK YOU so much for your help. This has, kind of, become a habit for me now.

Office timings are from 9:30 to 18:30 but the working hours are from 8:30 to 21:30, that is to say, employee cannot time stamp earlier than 8:30 & not later than 21:30. Next, if the employee takes a long lunch, say for 1 hour instead of 30 min & if he/she works till 19:00 instead of 18:30 then there is no issue as he/she has worked for 30 min extra which is fair enough. In fact, the excel only needs to check whether the employee has worked for minimum 8.5 hours or not; it need not differentiate between lunch break and working hours. I am extremely sorry for not pointing this fact out earlier.

Next, you asked what if there are more parts before and after lunch. As far as my supervising knoweldge & experience goes, such a thing has never happened so far. There has only been 1 part before lunch & 1 part after lunch = total 2 parts in all. So, you may not worry about that part.

Next, you were concerned about 15-min gap between timestamps. Well, that is perfectly alright. I mean why would an employee timestamp in and then timestamp out within 15 min. Does it make any sense? Atleast not to me. If it were me, I would just take the day off. So, in my opinion, I don't think it is a problem. However, you may change that 15 min gap to any other gap like 5 min or even 1 min, whichever time frame you feel is convenient for practical purposes. As far as fixing procedure is concerned, it is as mentioned below. Missed timestamp in till 18:30 = Employee is absent & missed timestamp out has 2 scenarios = automatic timestamp out at 13:30 if timestamp in is there before 13:30 & automatic timestamp out at 18:30 if timestamp in is after 13:30. I hope I didn't complicate it for myself again. Once again, my heartfelt gratitude for helping me.

P.S. I almost forgot to mention that in the reports section, let there be an additional column for overtime, that is, if an employee has worked for more than 8.5 hours during the working hours then the hours worked above 8.5 hours must be mentioned separately as I need to pay him/her extra for overtime. Sorry for missing out on that part.

 

[vletm]

vipul.dedhia66
Still sample with 'employees layout'.

You wrote that You'll control this with LAN.
That needs another file to do!
Only one (employees PC) can use this file in time!
You'll need other way to see data and get Your reports.
... coming soon ...

 

[vipul.dedhia66]

@vletm Once again, as usual, THANK YOU.

The latest version shows great potential. I am eagerly awaiting your next version.

 

[vletm]

vipul.dedhia66
Next samples ... with basic steps!
1) have a folder for those files
2) copy EmpBoss.xlsb and EmpTime.xlsb to that folder
3) create backup -folder to it (folder's name is 'backup')
4) copy Emp_tmp.xlsb to that backup-folder
Note! those won't work well without those steps!
Later ... EmpBoss.xlsb would be ... 'anywhere'.

EmpTime is like for employees and use as previous.
Emp_tmp is 'between' previous and next files (no need to open!).
EmpBoss is for getting report...
after I would know what kind of report would like to have.
> You've written
- no need to count daily hours
... but if over 8:30, then need to know overtimes ... hmm?
( yesterday 4hrs, today 10hrs ... 2hrs overtimes ... possible ... somewhere! )
- need to get 15days report ... (#4 Reply ... missing image)

>> need to get samples of needed layouts in EmpBoss-file!

 

[vipul.dedhia66]

@vletm Once again, THANK YOU for your help. It is greatly appreciated.

I followed your 4 steps but I am unable to use the files. Some error occurred when I opened the EmpTime.xlsb. I am attaching a zip file which contains the screenshot of how I have arranged the files in a separate folder named @vletm. Please let me know if I am missing out on something important.

As far as counting hours is concerned, the overtime hours need to be counted over a 15-day timegap & not on daily basis. For example - Suppose there are 12 working days in a 15-day gap (assuming there are 3 sundays) then the total working hours are 102 hours. So, the overtime hours should be counted if and only if the total working hours exceed 102 hours. The reason I wrote 8.5 hours earlier is assuming an employee is very regular. Sorry for not explaining that part in detail earlier. If possible, we can have 2 columns in report, one for daily hours worked by an employee and the next column could be cumulative hours worked so far in the 15-day gap. Also, I would like to know how to change Name1, Name2, etc to names of my employees as I do not provide employee codes to my employees.

Next hurdle which I could think of is what after happens after 15-day gap is over. I mean, would that automatically reset the excel file & if yes, what happens to the report file? Does that get reset as well or a separate file gets saved in backup folder. Please let me know.

Now, you asked for a sample layout of EmpBoss file. Give me a few hours & I will have it ready soon.

 

[vletm]

vipul.dedhia66
snapshots ...
Are You sure that @ is valid character for folder name?
> Make those folders to Your 'local hard' disk as normally.
>> If no help ... I try to check more after that.

>> EmpTime saves daily older than todays data to Emp_tmp and
keeps itself data max 7-days.
>> Soon ... EmpBoss gets data from Emp_tmp and keeps it for reporting ...
later the oldest would move to another file for save.

More later...

 

[vipul.dedhia66]

@vletm Once again, THANK YOU for your help. It means a lot to me.

I redownloaded the above files & named the folder as Employee Attendance Register. I have attached a zip file which contains the screenshot of the errors which I am facing (same one as before). It seems there is some other issue which I am not aware of. Maybe the Emp_tmp file should not be inside backup folder & just inside the main folder?

If possible, it would be better if the Emp_tmp file keeps data for 15 days instead of 7 days as anyways, I don't need it after 15 days.

Layout of EmpBoss file coming soon.

 

[vletm]

Hmm ... Now You have 'spaces' in folder-name! ... hmm?
I remember that would make some challenges too!

I have this kind of folder names - and this works.

I add for just testing purpose modified 'EmpTime.xlsb'-file.
It can be 'anyname' while You test it.
It would give information of that path to 'Emp_tmp.xlsb'-file.
Do You have more files like above snapshot?
Some of those should be there!

Emp_tmp is 'just' between those other two files!
Report will make with EmpBoss-file.

2nd EmpTime.xlsb has another variation ...

 

[vletm]

... ready or coming soon ... or something like below:

LEFT = side sums as written
NAME = in correct order (later)
RIGHT = day or daily hours as selected (date and number of days)
> sample: from 02-Oct-2017 and three days <

 

[vletm]

vipul.dedhia66
... with selections
1) copy Emp_his.xlsb to backup-folder
2) use new EmpBoss.xlsb as previous (or just those yellow cells!)

 

[vletm]

vipul.dedhia66
... ready or coming soon ... or something ...
Note: 'overtime calculation would need some modifications'