OBRC Time Calc Formula
Budget: $30 – $250 USD
I have an Excel sheet that is the result of a report in our Service software. I need to calculate two different elapsed times (arrival and complete). I have tried several formulas and calcualtions, to no avail...I get close, but not 100% correct, using such things as networkdays and networkdays.intl. Also, sometimes the service is same day (before midnight) and other times spans multiple days. Service can occur before or after operating hours, but elapsed time can only count 8am to 6pm.
We run 7 days per week, 8am to 6pm, wiht only 2 exceptions (Thanksgiving Day and Christmas day)
Review the attached spreadsheet.
I need to EXCLUDE 6pm to 8am from elapsed time hours
I need the elapsed time between column E and Column F into column K
I need the elapsed time between column E and column G into column L
I have used pen/paper to show results in column M and N to show you the desired result
Also, I need to be able to drag (or paste) the formula, as this report is run at least daily, and sometimes more, and the number of rows vary, depending on need.
formatting of any column can be adjusted to make your formula work easier...meaning split date/time into seperate columns, change date/time formatting, etc...
this report is variable, so the base premise I am shooting for is what is the formula....and then if I add/remove columns, what do I adjust in the formula to maintain the calculation.
We run 7 days per week, 8am to 6pm, wiht only 2 exceptions (Thanksgiving Day and Christmas day)
Review the attached spreadsheet.
I need to EXCLUDE 6pm to 8am from elapsed time hours
I need the elapsed time between column E and Column F into column K
I need the elapsed time between column E and column G into column L
I have used pen/paper to show results in column M and N to show you the desired result
Also, I need to be able to drag (or paste) the formula, as this report is run at least daily, and sometimes more, and the number of rows vary, depending on need.
formatting of any column can be adjusted to make your formula work easier...meaning split date/time into seperate columns, change date/time formatting, etc...
this report is variable, so the base premise I am shooting for is what is the formula....and then if I add/remove columns, what do I adjust in the formula to maintain the calculation.