Struggling with Attendance Calculations? Need a Simple Formula for Percentages!

Hi Members,

Here, I find myself in a situation where I have to submit the attendance, stating it in percentage (%), which includes the number of days present, absent, leaves taken, weekly offs, etc. It would be very helpful for me if anybody could send me the formula to solve this.

Warm regards,
Deeplika

Based on your query, I think I can help you out with Attendance MIS.

In the Excel sheet, design a format like this with the formula, for every employee/ID.

Name | Month | Working Days | Present | Leave % | Attendance % | Absence
-----|------|--------------|--------|---------|-------------|--------
RAM | August | 31 | 29 | 2 | =29/31*100 | =2/31*100
RAM | September | 30 | 30 | 0 | =30/30*100 | =0

Generally, a company with Mon-Fri working days has 22 working days in a month.
A company with Mon-Sat working days has 26 working days in a month.
Always consider Sundays, National Holidays, Declared Holidays, and Allowable SL, CL, EL as days present. Any excess days beyond the allowed quota should be marked as Loss of pay and marked as absent. You can further illustrate a graph or chart for each employee and also a chart for the total attendance report to provide a picture for the company as a whole.

For total attendance monthwise,

Date | Total strength | Present | Absent | % Present | % Absent
-----|---------------|--------|-------|----------|---------
4-Aug | 164 | 158 | 6 | =158/164*100 | =6/164*100
5-Aug | 167 | 164 | 3 | and so on

As and when new joiners come, the daily strength will increase, and when they resign, the strength will decrease accordingly. For any doubts, please reply to me.

I hope the formula is straightforward.

Regards, Chandru

Thank you, Chandru, that was a very informative post.

Dear Friends,

Please suggest to me what to do if an employee is working somewhere and has recently joined our organization with an existing ESI card and number. Do we need to apply for a new card and number, or will the old card and number suffice?

A quick reply would be helpful and appreciated.

Thanks

Dear Suraj,

In case of ESI/PF, it is transferable. If the new recruit is joining and is still under ESI and has a PF account, in both cases, his earlier ESI & PF account number can be transferred. However, you need to issue him the new ESI card as the company name will be changed; the ESI card number will remain the same, and the location and number of hospitals falling in that location will change accordingly.

In case of any queries, please revert.

Thanks,
Priyanka

Hi,

In the ESI Form-1 of Employee declaration, on the right-hand side, fill in Item No. 12. This asks for the details of the previous employer and your previous ESI Insurance No. If you want the old number to be transferred, then do not forget to fill this section. I have noticed that many employees are simply ignoring this as a 'headache' probably because they might have misplaced the details.

Regards,
Chandru