This is part of a series of articles on Importing Time Sheets with Excel.
As discussed in the topic Downloading an Import Time Sheet Template, you may choose to generate a "Daily" or "Weekly" Excel Template for a set of employees and a range of dates.
As illustrated below, the Daily Time Sheet template provides fields for entering employee data into separate Excel worksheets for each day. Information for Monday would be entered into one worksheet, Tuesday would be entered into another, and so forth. A Summary worksheet provides totals of all the daily worksheets.
As shown below, the Daily Time Sheet template includes a read-only Summary Worksheet that calculates the totals for the entire period. Since Makeup Pay is paid across the pay period (and not for a particular day), the Makeup Pay and Total Wages for each employee can be reviewed on the Summary worksheet before the file is imported into Time Portal. Please do not alter the formulas on this worksheet when editing the Excel file.
The following sections and columns are available on each of the daily worksheets:
- The Piece Rates section includes headings for up to ten different piece rates. If your workers are paid $1.00 for certain pieces and $2.00 for others, you can specify "1.0" and "2.0" in different columns as illustrated below. For each employee/date, simply enter the number of pieces for each specified piece rate.
Time Sheets which are created from an Excel file that includes multiple piece rates will include multiple Time Sheet records for each row of the Excel file. For each employee/date, one Time Sheet record will reflect the in/out times along with the first specified piece count/rate in the Excel row. Additional Time Sheet records will be created, without in/out times for the additional piece count/rate entries in the Excel row. Here is a snapshot of a Time Sheet with multiple piece rates.
- The Piece Work Hours column may be used to enter the number of hours that each employee spent picking pieces. When the Excel file is imported, an error message will be displayed if piece work hours are not provided for any employees who have piece counts. Piece work will be associated with a special "Piece Job" that is automatically created by Time Portal.
- The Regular Hours column may be used to enter the number of hours that each employee spent performing hourly work. Hourly work will be associated with a special "Hourly Job" that is automatically created by Time Portal. Note that it is possible to have piece work and hourly work on the same day. For example, if you specify 6-hours of piece work and 2-hours of hourly work, Time Portal will create separate Time Sheet Records representing six hours of piece work and two hours of hourly work.
- The Hourly Pay Rate column is automatically pre-populated when the Excel template is generated. As discussed in the topic Downloading an Import Time Sheet Template, this may be a specified value or it may be calculated based on various settings. The Hourly Rate is required and can be changed for each employee.
- The Hours Offered column may be used to specify the number of hours that were offered to each employee for specific dates. If left blank, Time Portal will determine hours offered based on various settings. Otherwise, a value can be specified to override the calculated value.
- The Start Time column may be used to specify a specific start time that will be used as the "Time In" for the first Time Sheet Record that is created for each employee/date. If no value is specified, Time Portal will default to 8:00 AM.
- The Break Start Time column may be used (along with Break Duration) to cause Time Portal to split Time Sheet Records across a break period. For example, if a worker starts at 8:00 AM, has eight piece work hours, 100 Pieces, a Break Start Time of 12:00 PM and a Break Duration of 0.5 hours, Time Portal will create two Time Sheet Records.
- 8:00 AM - 12:00 PM Piece Job, 100 pieces
- 12:30 PM - 4:30 PM Piece Job, 0 pieces
The two Time Sheet Records will represent eight total hours with 100-total pieces.
- The Break Duration column may be used (along with Break Start Time) to cause Time Portal to split Time Sheet records across a break period as described above.
- If you are tracking locations, the Location Name column may be used to specify a location name for the employee/date. Time Portal will ignore location names that do not match location names already in the system.
- If you are tracking crops/varieties, the Crop/Variety Name column may be used to specify a crop/variety for the employee/date. Time Portal will ignore crop/variety that do not match crop/variety names already in the system.
- Total Pieces, Total Hours, Piece Pay and Regular Hours Pay are calculated with Excel formulas and should not be altered when editing the Excel file.
- Piece Minimum Wage Rate and Hourly Minimum Wage Rate are pre-populated with values when the Excel template is generated and should not be altered when editing the Excel file.
- Minimum Pay is calculated with an Excel formula and is based on Piece Hours and Piece Minimum Wage Rate, along with Hourly Hours and Hourly Minimum Wage Rate. This formula should not be altered when editing the Excel file.
- Makeup Pay is calculated with an Excel formula and is based on Minimum Pay, Piece Pay and Regular Hours Pay. Time Portal calculates Makeup Pay for the pay period and not as the total of daily amounts which are shown for information only. In the illustration below, notice that the productivity later in the week more than made up for the first two days and therefore, no makeup pay is paid. The formulas for Makeup Pay should not be altered when editing the Excel file.
- Total Wage is calculated with an Excel formula and is based on Piece Pay, Regular Hours Pay and Makeup Pay. As mentioned above, Makeup Pay (on employee pay checks) is calculated for the pay period and not by the day. Therefore, the daily Total Wage is for information only but the weekly total (shown on the Summary worksheet) is what will be paid to the employee.
- Crew Code is pre-populated when the Excel template is generated and should not be altered when editing the Excel file.
- Deductions is not currently used by Time Portal.
As the worksheet is filled out, Excel formulas will calculate various values including the total wages for all employees which is shown at the bottom of the Summary worksheet.
The topic Creating a New Import describes how to import the Excel file which generates a separate Time Sheet for each day. When the file is imported, Time Portal computes Total Wages for all employees and compares it with the value that is calculated by Excel. The article What Could Cause a Totals Mismatch provides a discussion of the cause and resolution in cases where these values do not match.
Please sign in to leave a comment.