4 Replies Latest reply: Jul 21, 2015 9:00 PM by Ryan Minaker

# How to average hours between a start and end date?

I'm wondering if anyone can help me with creating a chart that will show hours averaged over the duration of a start and end date?

I understand the basics of the master calendar and have created this. I've also been able to calculate the business days/hours between the start date and end date. My problem is that I can't figure out how to average the estimated hours across a span of months and get the months to show up on the chart.

I've been reading though a lot of the community posts and perhaps IntervalMatch is what I need to use, but not sure how to get that to work.

Thanks!

Source data:

 StartDate EndDate EstimatedHours ProjectName Staff 7/1/2015 11/30/2015 500 Project A Bill 8/1/2015 09/30/2015 300 Project B Bob

What I'd like my chart to look like:

• ###### Re: How to average hours between a start and end date?

You could try something like this:

sum(EstimatedHours) / count(distinct Month)

if you have connected your StartDate and EndDate with the master calendar: IntervalMatch.

- Marcus

• ###### Re: How to average hours between a start and end date?

Thanks for pointing me to that link. I'm determined to become expert on how all this works!

R.

• ###### Re: How to average hours between a start and end date?

I would consider breaking down the EstimatedHours to the smallest granularity needed using a WHILE loop, as demonstrated here:

Creating Reference Dates for Intervals

```SET DateFormat = 'M/D/YYYY';

INPUT:
LOAD Recno() as ID, * INLINE [
StartDate, EndDate, EstimatedHours, ProjectName, Staff
7/1/2015, 11/30/2015, 500, Project A, Bill
8/1/2015, 09/30/2015, 300, Project B, Bob
];

ID_x_Dates:
EstimatedHours / (EndDate - StartDate + 1) as DailyEstimatedHour,
Date(StartDate + IterNo() -1 ) as ReferenceDate
Resident INPUT
While IterNo() <= EndDate - StartDate + 1 ;
```

Here I used a date as finest granularity, which you can easily aggregate to week / month / quarter using a master calendar connected to ReferenceDate. If you only need a Monthly view, you can also change the code to iterate over Months, not dates.

• ###### Re: How to average hours between a start and end date?

Thanks so much for this. I applied it to my real data and it works great! As Marcus mentioned, I was sure the solution had something to do with intervals, but just wasn't sure how it worked.

Another question for you -- if I wanted this to span the minutes/hours only over work days where would be the best place to apply Networkdays function? The loadscript, master calendar, custom dimension?

Thanks!

R.