Handling the “Extra Day of Pay Calculation” for many employers is vital for your organization’s budget. These additional days of compensation will impact your budget and it will modify an employee’s payroll. So, calculating these extra days of pay will be important to your organization.
- Spreadsheet or google sheet
- Employee database
- Employee ID associated with each employee
- Index Match Formula
- Extra Day of Pay Formula
Daily Rate Step 1.
The spreadsheet or a google spreadsheet will be required to run this. The goal when building a daily rate calculator is to have a consistent dataset from bargaining, payroll or an HRIS system. I prefer google spreadsheets to track who is using the spreadsheet and to collaborate quickly with other team members.
Daily Rate Step 2.
Consistent data or “raw data” is key for this calculator to always perform accurately. Know where this data will consistently be pulled from to insure that proper calculations are always performed. Set up a “raw data” tab where you can copy and paste the raw employee data.
Daily Rate Step 3.
Make sure that you have a unique ID that is tied to each employee in the database. This will allow you to run Steps 4 and 5 accurately.
Daily Rate Step 4.
Vlookup formulas do not look to the left. If your unique employee ID is to the left of your data, then perfect. If it is not then run the following formula. =index(data you want to lookup,match(the ID #, to the ID# in the raw data,0)). Run this formula for each column of data you need to perform step 5.
Daily Rate Step 5.
The extra day calculation is straightforward. Total Salary (divided by) Number of Workdays = Daily Rate (multiplied by) FTE (multiplied by) Number of Extra Days. Many times the FTE does not apply as a full day is given and the FTE column will be blank. Therefore, we need to write a quick =if statement. The formula below states, “if the FTE column is blank then make it appear as a 1.0 FTE for this calculation.”