Arts Umbrella Payroll Checklist
Year Pay Period (PP) Month Start Date End Date 1. Preparing the Submissions Spreadsheet
Order Task Check Go to Programming Confidential > FileMaker Databases. Copy (don't move) the current faculty schedule Excel workbook to the current payroll folder in Programming Confidential > Payroll. Rename the copied workbook to [current year] PP[current number] Submissions - PROG.xls. Open this new workbook. Delete the Schedule A and Daily Hours worksheets. Rename the Database worksheet to Timesheet. Go to the Timesheet worksheet. Delete row 1 so that all column headers shift up and become the new row 1. If not already applied, apply filters across row 1. Ensure each column with data has a filter in its top cell. Using a filter, sort column B (Status) from A to Z. Delete all rows marked Cancelled, Overflow and Pending in column B (Status). Delete columns A through G, or all columns left of column H (Employee Number). Apply All Borders to the entire worksheet. Insert a column to the left of column D (Start Date), creating a new column D. In cell D1, type Day. In cell D2, type =text(e2, "ddd"). Drag the fill handle on cell D2 down the entire column D. Sort column E (Start Date) from Oldest to Newest. Filter column E (Start Date) to only show dates that fall after the current pay period (if any). If any start date(s) fall after the current pay period, delete their associated row(s). Clear all filters. Sort column G (End Date) from Oldest to Newest. Filter column G (End Date) to only show dates that fall before the current pay period (if any). If any end date(s) fall before the current pay period, delete their associated row(s). Clear all filters. Sort column E (Start Date) from Oldest to Newest. Sort column D (Day) from A to Z. 1.1. Creating Rows for Each Day of the Pay Period
Order Task Check Filter column D (Day) to only show rows marked Monday. For every row with a Start Date before the current PP, change that start date to the 1st Monday within the PP. Filter out any remaining rows with start dates that are not the first Monday within the PP. Select all remaining rows. Fill every row selected in the previous step with a single colour other than red. If the Monday of the following week also falls within the current PP, copy every row assigned a colour in the previous step. Paste the copied rows directly below the last row containing data. In each of the newly pasted rows, change the Start Date to the Monday of the following week within the current PP. Fill all rows updated in step 8 with a colour other than red or any other previously selected colour. If the Monday of the week following the Start Date entered in step 8 also falls within the current PP, copy every row assigned a colour in step 9. Paste the copied rows directly below the last row containing data. In each of the newly pasted rows, change the Start Date to the Monday of the following week within the current PP. Fill every row updated in step 12 with a colour other than red or any other previously selected colour. Clear the filter for Start Date only. Select any uncoloured rows that share the same Start Date. (If rows with 2 Start Dates are present, select only those with the earliest Start Date.) Fill every row selected in step 15 with a colour other than red or any other previously selected colour. If the Monday of the week following the Start Date of rows assigned a colour in step 16 also falls within the current PP, copy those rows. Paste the copied rows directly below the last row containing data. In each of the newly pasted rows, change the Start Date to the Monday of the following week within the current PP. Fill every row updated in step 19 with a colour other than red or any other previously selected colour. If there are any remaining uncoloured rows, select them. These will fall on the last Monday within the PP. Fill every row selected in the previous step with a colour other than red or any other previously selected colour. Clear all filters. Repeat steps 1–23 for Tuesday instead of Monday. Repeat steps 1–23 for Wednesday instead of Monday. Repeat steps 1–23 for Thursday instead of Monday. Repeat steps 1–23 for Friday instead of Monday. Repeat steps 1–23 for Saturday instead of Monday. Repeat steps 1–23 for Sunday instead of Monday. Clear all filters. Confirm that all Start Dates now fall within the current PP. Confirm that all rows are filled with colour before continuing. 1.2. Marking Unnecessary Rows for Deletion
Order Task Check Delete column D (Day). Confirm that column D is now the Start Date column before continuing. Sort column D (Start Date) from Oldest to Newest. Filter column D (Start Date) to only show rows that fall on stat holidays or building closures (if any). If any rows have start dates that fall on stat holidays or building closures, select those rows. Fill every row selected in the previous step with the colour red. If any rows have start dates that fall on stat holidays or building closures, type Delete in column O (Substitution/Deletion) for each of those rows. If any rows have start dates that fall on stat holidays, type Stat Holiday under column N (Comments) for each of those rows. If any rows have start dates that fall on building closures, type Closure under column N (Comments) for each of those rows. Clear all filters. Sort column F (End Date) from Oldest to Newest. Filter column F (End Date) to show only rows with end dates that fall within the current PP (if any). If any rows have start dates that fall after their end dates, select those rows. Fill every row selected in the previous step with the colour red. If any rows have start dates after their end dates, type Delete in column O (Substitution/Deletion) for each of those rows. If any rows have start dates after their end dates, type Class Ended under column N (Comments) for each of those rows. Clear all filters. In cell F2, type =d2. Drag the fill handle on cell F2 down the entire column F. Confirm that the end date now matches the start date in each row (e.g. End Date in F5 = Start Date in D5, End Date in F6 = Start Date in D6). Sort column K (Notes) from A to Z. Filter column K (Notes) to only show rows that state there is No Class on one or more dates. If the Start Date for any row matches a No Class date in that row's Notes, select every such row. Fill every row selected in the previous step with the colour red. If the Start Date for any row matches a No Class date in that row's Notes, type Delete in column O (Substitution/Deletion) of every such row. If the Start Date for any row matches a No Class date in that row's Notes, type No Class on This Date in column N (Comments) of every such row. Clear all filters. Filter column K (Notes) to show rows that state classes only run on specific dates. If the Start Date for any row does not match a class date in that row's Notes, select every such row. Fill every row selected in the previous step with the colour red. If the Start Date for any row does not match a class date in that row's Notes, type Delete in column O (Substitution/Deletion) of every such row. If the Start Date for any row does not match a class date in that row's Notes, type No Class on This Date in column N (Comments) of every such row. Clear all filters. Sort column E (Start Time) from Smallest to Largest. Sort column D (Start Date) from Oldest to Newest. Sort column B (Employee Name) from A to Z. Filter column L (Responsibility) to show only Outreach and Offsite entries for all locations. Look for any rows marked for deletion in column O (Substitution/Deletion). For each row that meets the criteria in the previous step, ensure its associated Class Time or Extra Time row is also marked Delete in column O (Substitution/Deletion). Ensure that each Class Time or Extra Time row marked Delete in the previous step is also filled with the colour red. Clear all filters. Save and close the workbook.