2b / How To Edit & Refine The Delegated Hours Spreadsheet - Creative

Editing The Delegated Hours Spreadsheet

The following steps show you how to edit and refine the data you have entered from the Detailed Time Report into the Delegated Hours_Creative Team Spreadsheet. You must follow this process in order otherwise the data may get skewed and you will not have an accurate outcome when you add the calculations at the bottom.

Select all data (Ctrl + A)
From the ribbon at the top of the spreadsheet select Top Align and Align Left from the Alignment menu options
Select the Project Code column (D), right-click and select Cut. Click on the Billable column (H), right-click and select Insert Cut Cells
Rename the Project Code column as Delegating VA and resize the Column Width to 15
Select the Task column (now column D). Select Sort & Filter from the editing menu options, then Sort A-Z from the dropdown. Click on the Sort button on the pop-up that appears
Go through and delete all rows where the task IS NOT _Virtual or Tasks from Rochelle ie. Admin; Annual, sick or bereavement leave; Downscaled time (office use only); IT Issues; Team Meetings; Training; Travel to clients; etc
Once you confirm that only _Virtual or Tasks from Rochelle rows are left, select the Tasks column, right-click and select Delete
Select the Notes column (now column D), right-click and select Column Width. Change it from 255 to 40 and click on OK
Select the First Name column (H). Select Sort & Filter from the editing menu options, then Sort A-Z from the dropdown. Click on the Sort button on the pop-up that appears
Delete all rows that have the First Name of a NZ team member, NZ contractor, or EA/Internal Global VA - this will leave you with lines for only the Creative/Design Global VA team, who at this time are Ginna and Judy
Select the Project column (C). Select Sort & Filter from the editing menu options, then Sort A-Z from the dropdown. Click on the Sort button on the pop-up that appears
Select the Client column (B). Select Sort & Filter from the editing menu options, then Sort A-Z from the dropdown. Click on the Sort button on the pop-up that appears
Select the Billable? (G) & Invoiced? (H) columns, right-click and select Delete
Select the Last Name (I), Roles (J) and Employee? (K) columns, right-click and select Delete
Select the Cost Rate (K), Cost Amount (L), Currency (M) and External Reference URL (N) columns, right-click and select Delete
Select all the cells underneath Delegating VA (column F) down to the last line of notes. Select Fill Color from the editing menu options, and under Standard Colors pick yellow
Select the next free cell at the bottom of the Hours column. Select AutoSum from the editing menu options, then hit enter on the keyboard

The Delegated Hours Spreadsheet should look something like this at this point:

Once you have completed editing the spreadsheet, you are ready to refine it and check the VA assigned to each of the highlighted boxes. This should mostly be pre-populated from the Project Code in Harvest, but we always do a final check to make sure it is accurate.

Use the Client & Staff Register in Airtable to check who the VA for each client is if you are unsure. Note that some clients may have a Head VA as well as a Creative VA, so often there are multiple timers set up under a client so we easily distinguish whether the task was assigned by the Head VA or by the Creative VA.

Remember: If you are unsure of who the delegating VA is, ASK - either the head VA for the client, or their Team Leader if they are not available at the time.

Refining The Delegated Hours Spreadsheet

Assign a VA to each of the highlighted boxes in the Head VA column
Go to a previous month's tab and scroll to the bottom of the page
Select the cells that have the Month, Delegated and Available, and the list of team member's names (as shown in the pic below) and copy (Ctrl + C)
Go back to the tab for the month you are editing
Click on the 3rd empty cell down in column A, beneath the data pasted from the Detailed Time Report (this will differ each time depending on the amount of data brought over from that report)
Paste the data (Ctrl + V) into that cell
Change the name of the month to the one you are editing for
Check that the sum at the bottom of the Hours column matches the sum at the bottom of the Delegated column that you brought over from the other tab (as highlighted in the pic below). If it doesn't match, check the formulas in the section you brought over to make sure they include all of the rows above it
Once the hours match you are ready to save the spreadsheet and post the number/s in chat to the applicable team member

The bottom section of the Delegated Hours Spreadsheet should now look something like:

Google Sites

Report abuse