good morning all,
I'm wondering if somebody can hep me with this one:
we have 60 field engineers, we receive daily electronic timesheets, which records travel to site, time on site, travel home etc, I've simplified the attached spreadsheet's to just show time on site to avoid confusion.
no conflictions.xls - this is where an engineer travels from site to site, filling in one job per site, this is easy for us to workout utilisation. no issue with this (attached to help understanding) you will see this engineer spent 3hr 23m in total across 6 x sites.
has conflictions.xls - this is where we are struggling, this particular engineer completed 21 jobs, however the times overlap because the site may have two jobs, which makes it look like he has worked over 27 hours.
is there any formulas we can use on the "has conflictions" spreadsheet to achieve a true value.
please take a look and thank you in advance, your help will be greatly appreciated.
link to spreadsheets (google drive)
I'm wondering if somebody can hep me with this one:
we have 60 field engineers, we receive daily electronic timesheets, which records travel to site, time on site, travel home etc, I've simplified the attached spreadsheet's to just show time on site to avoid confusion.
no conflictions.xls - this is where an engineer travels from site to site, filling in one job per site, this is easy for us to workout utilisation. no issue with this (attached to help understanding) you will see this engineer spent 3hr 23m in total across 6 x sites.
has conflictions.xls - this is where we are struggling, this particular engineer completed 21 jobs, however the times overlap because the site may have two jobs, which makes it look like he has worked over 27 hours.
is there any formulas we can use on the "has conflictions" spreadsheet to achieve a true value.
please take a look and thank you in advance, your help will be greatly appreciated.
link to spreadsheets (google drive)
