Forum Discussion
Percentage of attendance
Hi all, How can I calculate the percentage of attendance using excel?
Are you looking for an individual's actually attendance vs their scheduled amount of time? An individual's attendance vs the entire group's attendance? Or something else?
Adjust column I so that Shift Length is a number. For example, 9h should instead be just 9. This can be accomplished by highlighting the range I4:I123 pressing Ctrl + F, replace "h" with (leave blank).
Place the following table below your data:
I will assume that all absences are excused. The formulas for "Andrea Reyes" are as follows...
Scheduled Time:
=SUMIFS($I$3:$I$123,$A$3:$A$123,$A147,$L$3:$L$123,">0")
Actual Time Worked:
=SUMIFS($L$3:$L$123,$A$3:$A$123,$A147,$L$3:$L$123,">0")
% of Sch. Worked
=SUMIFS($L$3:$L$123,$A$3:$A$123,$A147,$L$3:$L$123,">0")/SUMIFS($I$3:$I$123,$A$3:$A$123,$A147,$L$3:$L$123,">0")
or simply
=$C147/$B147
Edit: I should note this formula does not account for break time.