Comparing the time contractors plan to work VS actual hours on a dashboard

Corey McCarren

Hello all -- I want a sheet which will be used for contractors to log their planned billable hours, their actual hours, and their planned time away for a given week. The spreadsheet will have multiple weeks in it, and contractors will fill it out for several weeks at a time (the planned hours and planned time away). As they go along, they will fill out the actual hours worked

I would like to create a formula which will show:

The variance for consultant availability compared to what they actually worked by week

The variance for consultant availability compared to what they actually worked over all weeks

I would then like to create some sort of report and chart with that data, so someone can easily look and see contractor X on average works 5 hours less than his planned hours over a number of weeks (so, maybe the spreadsheet is for 6 weeks at a time, and then it is refreshed. In this case, for a 6 week time period), and also per-week what the variance is per person. Below is what the sheet currently looks like:

Thank you for any guidance! This use-case is a bit outside my wheelhouse, so I am trying to come up with a solution.

Bassam Khalil

Hi @Corey McCarren,

Please try the following

Setting Up the Sheet:

Create a sheet with the following columns for each contractor:

(Contractor Name - Date or Week Number - Planned Billable Hours (for the week) - Actual Hours Worked (for the week) - Planned Time Away (for the week) - Variance for the Week (Calculated as planned minus actual) - Cumulative Variance (This will be calculated separately)

Rows: Each row represents a contractor's weekly data. For multiple contractors over several weeks, you will have multiple entries per contractor.

To calculate the weekly variance between planned hours and actual hours worked, use the following formula in the 'Variance for the Week' column:

=[Planned Billable Hours]@row - [Actual Hours Worked]@row

This formula subtracts the actual hours worked from the planned billable hours to show the variance for each week.

Cumulative Variance: To calculate the cumulative variance over all weeks, you'll consider using a Report or Metric Sheet to summarize this data. For example, create a Metric Sheet with a column for each contractor and use SUMIFS functions to sum the weekly variances for each contractor across the dataset.

Creating Reports and Charts

Reports: To create a report that shows the variance per contractor over the selected period (e.g., 6 weeks),

Go to the Reports section and create a new report.

Select the sheet as your source.

Use filters to include the 'Contractor Name', 'Week', and 'Variance for the Week' columns.

You can group the report by 'Contractor Name' to see each contractor's weekly variances together.

Charts: To visualize the data:

Create a chart based on the report or directly from the sheet.

Select a bar chart or line chart to show the variance for each contractor over time. This visual will help identify trends, such as consistently under or overestimating work hours.

For cumulative variance analysis, use the Metric Sheet or a summary section within your sheet. You can then create a chart directly from this data to visualize the average weekly variance per contractor over the period.

Additional Tips

• Consider setting up alerts or reminders for contractors to update their hours weekly.
• Use conditional formatting to highlight significant variances, making it easier to spot issues at a glance.