You need to go deep in to Google Spreadsheets to realise how powerful and useful this spreadsheet application is. I’ve written a row articles in our Google Sheet section on this site to enable our precious readers like you to utilise some of the awesome features this spreadsheet application provides. This time let us learn how to create a staff attendance sheet using Google Sheets.
Before begin with the tutorial to create a trendy staff attendance sheet, two things I wish to clear. The first one is we are discussing about how to create an attendance sheet using Google Sheet. We are not providing any custom templates here. So you should follow our tutorial to create an attendance sheet by your own. The second thing this is purely an attendance sheet to calculate your staff members or employees monthly present as well as absent details in order to prepare the pay slip. So it does not contain over time hours or man hours. If you want prepare that one separately and add to the payslip, not attendance sheet.
To prepare a staff attendance sheet using Google Sheets, you only require a simple spreadsheet function called COUNTIF. You can take the input data either from the staff attendance register or depend your office Biometric time attendance machine output.
When you create a staff attendance sheet using Google Sheets application, you should understand the following terms. The below is not the definition. I just put it to make things clear.
Sick Leave: Sick leave in payroll is the leave granted because one can’t attend office due to his or his near ones illness.
There may be questions in your mind like whether the sick leave is paid or unpaid? It’s normally paid leave and depends the labour laws in your country. Also in most of the cases the number of of sick leave is limited.
Casual Leave: This’s also a paid leave and limited to few days monthly and also depends your country or state labour low.
Privilege Leave: Carry forwarded leaves.
Then finally Present, Absent and Holidays which of course you know.
We are applying the below short codes to represent the above terms in our formula. They are P for Present, A for Absent, H for Holiday, SL for Sick Leave, C for Casual and PL for Privilege Leave.
Hope you won’t confuse with the above terms. You only need to follow the prevailing leave policy in your country.
Steps to Create a Staff Attendance Sheet Using Google Sheets Online Spreadsheet
Create a spreadsheet as below and apply the following COUNTIF functions in the red highlighted cells in your sheet.
In Cell AG8 the formula should be =COUNTIF(C8:AF8,”P”)
AH8 the formula should be =COUNTIF(C8:AF8,”A”)
AI8 the formula should be =COUNTIF(C8:AF8,”H”)
AJ8 the formula should be =COUNTIF(C8:AF8,”SL”)
AK8 the formula should be =COUNTIF(C8:AF8,”C”)
And finally in Cell AL8 the formula should be =COUNTIF(C8:AF8,”PL”)
Now copy paste all the formulas to below rows. This will populate total present, absent, sick leave, casual leave and privileged leave details in the respective cells automatically.