Excel is now a big part of every business and being skilled in using Excel Software will take your career to the next level.
There are two databases that you need to have.
Now, let’s begin with time keeping. In this sample, we will do the week’s payroll.
From your manual attendance sheet, you will be needing:
- Employee Name
- Date
- Time In
- Time Out
- Schedule
Using above listed data, your excel shall be able to give you the following results.
- Overtime – is the amount of time someone works beyond normal working hours
- Late – occurring, coming, or being after the usual or proper time:
- Under-time -working time that is less than full time or a required minimum
Need help? Contact me.
Let’s detailed the formula used.
- OVERTIME CALCULATION
To get the excess hours/overtime, I compared the schedule out which came from the schedule database from the actual time out.
If Actual time out (J3) is higher than Schedule time out (I3) then it will calculate the excess however in case that Actual time out is equal or less than the Schedule time out, then it will give us “0” or Zero as value.
However, we have a minimum of 30 minutes for us to pay for the overtime, therefore, we will use the additional formula which is below.
FLOOR( number, significance )
In the formula box, you will see that M3 or the result (from Actual time out less Schedule out) is using FLOOR function with a significant value of “00:30” then the whole formula is converted into numeric value by multiplying by 24(hours a day).
2. LATE
To calculate the late, I used two columns.
The first if is comparing the Schedule In(E3) to Actual Time In of the employee(F3). In case, Schedule In(E3) is less than the Actual time in(F3), then it will give value to our late (G Row) in time format.
The second column is for converting the value of Late in Time format into Numerical Value. Late (G3) will be multiplied by 24 for hours then for 60 for minutes value while the multiplication for the -1 is for to give us positive value.
Need help? Contact me.
3. Under time
For the calculation of under time, we compare if Schedule Out (I6) less than Actual Time Out(J6) is yes then the value of Under time (P6) is “0” else the value of Under time (P6) will be Time Out less Schedule time out.
Being skilled in excel will open your opportunity and broaden your perks. You do not have to love numbers but you have to be logically keen to formulas.
I’m going to post soon an additional topic about excel which will teach you how to use the above details in calculating the payroll itself.
In case you need some assistance with excel or a helping hand, please do not hesitate to contact me.
Grace Anne Alvarez 