4 Replies Latest reply on Feb 19, 2013 10:24 AM by Bernardo Roschke

# Duration Calculation

I'm trying to calculate time duration of work orders [WO] based on start and end times. I have the following formula, DATEDIFF('minute',[Start Time],[End Time])/60 which works. But there are cases when a work order is split into multiple sub-work orders. This formula will multiply time duration by the amount of sub work orders.

The attached shows QTY, which I'm trying to match via my duration calculation. Work Orders will not be in the view but are only present to show the effect of sub work orders on the Duration Calculation. The desired result is that QTY and Duration match without WO in the view.

Thanks and let me know if I was not clear.

• ###### 1. Re: Duration Calculation

Hi Bernardo, thanks for posting the sample workbook. I'm not clear on what you're trying to accomplish. Do you want the result to look like this:

Or this:

Or this:

And when you say you don't want WO in the view what do you mean? You don't want it showing in the table? Or you don't want it in the view at all? And why? Thanks,

--Shawn

• ###### 2. Re: Duration Calculation

Hi Shawn,

Thanks for replying. I'm looking for the result in your third example [duration along cell]. These are hours and I'm trying to calculate resource utilization. Some times work order is split based on client billing. So I do not want to overstate the duration based on if a work order was split for billing purposes. Let me know if that makes sense.

The final result will be an aggregation of all work orders per resource. I only put in table example to show the behavior.

• ###### 3. Re: Duration Calculation

Bernardo, this will get you started, I trimmed your sub-WO numbers off the end:

LEFT([WO],9)

Then I did a LOOKUP() to use as a filter:

ATTR([Trim WO])=LOOKUP(ATTR([Trim WO]),-1)

However, now that you're wanting to aggregate to a different level, you're going to need to read Jonathan's series on Grant Totals. I'm not a text table guy, so someone else is going to need to help you.

--Shawn

• ###### 4. Re: Duration Calculation

Thank you for your assistance. I think this will get me in the right direction. Much appreciated.