Attendance Tracking: How Do You Handle Different Types of Employee Absences?

Hi,

Please check out the monthly attendance tracker. Just for clarity, leaves are posted in four ways:

1. Planned
2. Unplanned
3. Came but left - Came to the office but left for an emergency, etc.
4. Uninformed - Unplanned with no info to the office.

I would like to hear your opinions on the same.

Please see below for more understanding.

I use it this way. You may use your own rules and regulations for the same according to ur needs.

Methodology:

All leaves taken by anyone will be segregated in 4 types:



a) Planned - Employee has to inform at least 24 hours before to get one. If 3 people are already on planned leave then, no one would be entitled for any leaves.

b) Unplanned- Due to any reason, employee has not been able to come to office however he has informed his senior about the same. E.g. sick leave.

c) Uninformed- Employee neither informed anyone nor came to the office.

d) Came but left- Employee came to the office however has to leave due to some reasons. E.g. emergency at home, not feeling well etc.

General rules to be followed:

a) On any day, we could only afford absenteeism below 10% which means not more than 4 employees can be on leave from the whole process. (including TL, Trainer, DPE or MIS expert).

b) Due to above rule we should make sure that we don't give planned leave to more than 3 employees in a day and keep 1 leaves for any possible unplanned/uninformed/came but left leaves).

c) Every possible effort should be taken care of to make sure that daily, weekly and monthly attendance reflects Green or Yellow and there should be no Red.

Green - Absenteeism below 5.5%

Yellow - Absenteeism between 5.5% to 9.5%

Red - Absenteeism above 10%



Benefits:- Better control of attendance.

Backups can be created well in time.

More accountability for agents towards performance and attendance to office

Data tracking for performance appraisals.

Thanks & regards

Gurpreet Singh Mann

Hi Gurprit,

Can you explain to me how to use this monthly attendance tracker? What does 0 stand for? How do I mark present, etc.? Please help.

Regards,
Rahul Verma

Hi Rahul,

"0" stands for a person being present and not on leave. Suppose a person has taken leave on the 5th of the month. In that case, you will select the dropdown menu on the 5th in front of his name and choose the type of leave. This will be added to his monthly total and the daily total of the team.

Thanks,
Gurpreet Singh

Hi,

Thanks for the appreciation from all of you. Please check this out.

Here is more towards attendance tracking. It's the next big thing. Yearly attendance tracking to see how good an agent is in his attendance by calculating through simple formulas (at the end columns).

Once you have updated the monthly attendances, you can collate the data in this file and then have attendance performances defined for each employee at the end of the appraisal period.

Just a brief of the file:

New things in this file:-

Date of joining of each employee

Non-working days in the initial month - An employee might not join on the very first day of the month. We need to calculate the number of working days already skipped for the month and update the non-working days here manually.

For example:

I join an organization on the 15th of September. To keep my attendance data on par with everyone, you will note down my non-working days, which is 10 (we have to exclude weekends or scheduled roster offs), and enter this figure in this column. This will accurately bring me two formulas at the end.

Working days per month - days excluding any holidays or scheduled offs/weekends.

In the file, there are 4 examples:

1) Person joined on 9th Jan - 5 non-working days

2) Person joined on 7th Jan - 0 non-working days

3) Person joined on 9th July - 129 (Jan-Jun) + 5 (July) = 134 non-working days

4) Person joined on 1st Jan - 129 (Jan-Jun) non-working days

You need to update here the number of planned/unplanned/came but left/uninformed leave for each employee from their monthly data.

Absolute attendance: - Total number of leaves (any type) divided by total workable days for each agent.

Planned attendance: - Tracks unplanned leaves of agents, keeping out the already planned leaves from workable days.

Benefits:

A) Tracks individual attendance.

B) Gives an idea about how healthy the attendance data we have.

C) Tracks two types of attendance:

1. Absolute attendance: - Total number of leaves (any type) divided by total workable days for each agent.

2. Planned attendance: - Tracks unplanned leaves of agents, keeping out the already planned leaves from workable days.

D) Data for performance review.

E) Highlighting the grey areas and giving opportunities to focus on people with unhealthy attendance.

Thanks,

Gurpreet Singh Mann

Also in my company, for associates who work on a shift basis, there is a fixed working time of 9.00 hours per day. Therefore, we follow the same format with 2 additional rows for In time and Out time. At the end of the month, the concerned authority also calculates if the associate is eligible for overtime pay or how many hours they have taken off on any particular day, etc.

Regards,
Vandana

Hi Gurpreet,

I have gone through this file, but still I am not clear how to use this. Can you please explain this so that we can feel comfortable in using this?

Thanks,
Bibhutosh Bhadauria

Bibhutosh Bhadauria,

It is very easy to use. Please take your time and study it. I did, and I can't close it each time I open it in the office; it's amazing. He is a Guru.

We need more innovations like this. We should all be challenged to create something different every time; that will make the forum more educative and interesting. I'm happy to be among learned friends.

Lockwood

Hi all,

Thanks for showing so much interest in my work. I am really excited to see such a response.

Lockwood - Really appreciate it.

Rameshbala - Can I have your email id so that I can send it to you?

Bhavani Prasad - Mailed to you.

Sumit/Shreekanth - This is a default format, and you will have to make amendments as per your requirements.

Please check these points:

1) If anyone has not taken leave, it will remain as 0 by default as it is counted as 0 leaves. However, if someone has taken a leave, you will mark the leave by selecting the type of leave from the drop-down menu.

For example, suppose a person has taken leave on the 5th of the month, then you will select the drop-down menu on the 5th in front of his name and select the type of leave. This will add up to his monthly total and daily total of the team.

2) Please go through my previously given detailed comments.

3) For other points, kindly be specific about what you need to know about the format.

Bibhutosh Bhadauria - Kindly go through the detailed comments I have given with the attachments and confirm which point needs to be clarified.

Subhashmalik - I hope this file gives you a solution. If it doesn't, then please confirm what more is required or how it's required.

Actions required: Apart from entering monthly details of leaves, now you just have to put the number of days a person has worked on holidays (any holidays/Sunday), and then it will add up. Formulas have been amended to reflect the holidays working as planned working days.

Thanks,

Gurpreet Singh

Hi,

You have used words like planned, unplanned, uniform, etc., in your text. Could you please explain the meaning or concept behind these terms?

Regards,
Divya

Dear Sir,

Please help me understand how to use this format. It is a very nice format, and I would like to apply it practically. My email ID is hirend@suzlon.com; you can advise me using this ID as well.

Regards with thanks,
Hiren

Great work, but I have one confusion. What if someone is present, then what should he/she select from the sheet - Plan 1 or what? Please let me know, as I am a bit confused.

Certainly! Here is the revised text with corrected spelling, grammar, and punctuation, as well as proper paragraph formatting:

Can you please help me with one thing? If someone is present, what are we supposed to enter in the sheet? I am a bit confused about this. Help me because I have to do this. Thank you.

Hi, this is Pramod.

Great information! I have a little problem. Can you elaborate more on it with examples?

Waiting for a reply.

Regards,
Pramod

Hi Gurpreet,

What does "planned" and "unplanned" mean? Don't you think it is unprofessional to write "planned" and "unplanned"?

Smita

Hi,

The attendance format is not really good because for every month we have to create a new Excel sheet, and in year-end, how will we come to know about the leave availed by employees such as CL, PL, ML, etc.? Also, what are planned and unplanned leaves? It's not a perfect phrase; you should use CL, PL, ML, AB, HD. How to carry forward earned leaves? 🤔

Thanks & Regards,
Preeti

Herewith, I have attached the tracking sheet. Please provide your suggestions in response.

I liked the sheet. It can be very useful. I feel there could be some additions.

You are calculating absenteeism. Can't we calculate the days he was present and get it displayed, and percentage calculated weekly and monthly. I have such a requirement. Could you help me with that? My email is [Login to view].

Thanks,
Rajeev

Dear sir,

Please send me the attendance sheet format in Excel.

Thank you.

Regards,
Pawan Kumar Chauhan

Hi Gurpreet,

Indeed, a good attendance tracker. However, in the formula =SUM(E46:I46)/205, 205 stands for what.

Regards

Hi,

I noticed that there are 2 values, i.e., (1, 0.5), against the following fields:
1. Planned
2. Unplanned
3. Uninformed
4. Came but left

Could you please clarify the reason for these values? I would appreciate it if you could resolve this query as soon as possible.

Regards

Hey, that's such a wonderful piece of information. I had been looking for something like this to keep track of my 1000-workforce in the field. :) Thanks once again, Sunita Saxena

Hi, I suggest that for easier access to leave availment tracking of each employee, ask your IT personnel to create a database. Just provide the details that are going to be included in the said database. This is what we currently have - making it easy to track each employee's remaining leaves.

For our attendance, we are using biometrics this time since I am doing the attendance and late tracking monthly.

I want to calculate attendance in an Excel sheet with a formulated approach. Please, can I get help with this? Thank you.