Average Span of Dates, by Individual Month

[gexexcel]

Good afternoon,

I've been playing with some formulae like COUNTA() and TODAY() for days and I was hoping one of you might be able to propose a simple solution!

I have a database of landmark dates for multiple installation projects. Example:

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]Location[/TD]
[TD]Quoted[/TD]
[TD]Ordered[/TD]
[TD]Delivered[/TD]
[/TR]
[TR]
[TD]10602[/TD]
[TD]11/02/2015[/TD]
[TD]11/05/2015[/TD]
[TD]11/09/2015[/TD]
[/TR]
[TR]
[TD]4542[/TD]
[TD]11/08/2015[/TD]
[TD]11/12/2015[/TD]
[TD]11/15/2015[/TD]
[/TR]
[TR]
[TD]235[/TD]
[TD]12/19/2015[/TD]
[TD]12/25/2016[/TD]
[TD]12/27/2016[/TD]
[/TR]
[TR]
[TD]8281[/TD]
[TD]01/26/2016[/TD]
[TD]01/29/2016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5222[/TD]
[TD]01/21/2016[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


What I want to do is create a scorecard showing how long the average span was for each month, I need a formula for the cells with red text. Example:

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Measure[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[TD]Jan[/TD]
[/TR]
[TR]
[TD]Time to Order Avg (Column C - Column B)[/TD]
[TD]3.5 days[/TD]
[TD]6 days[/TD]
[TD]3 days[/TD]
[/TR]
[TR]
[TD]Time to Deliver Avg (Column D - Column C)[/TD]
[TD]3 days[/TD]
[TD]2 days[/TD]
[TD]X days[/TD]
[/TR]
</tbody>[/TABLE]

As indicated in the first table, there won't always be data in each cell while work is in process. If it's possible to ignore those with blank data that would be ideal, but if not perhaps assume all blanks are TODAY()? Also perhaps we can throw in a condition to omit holidays / weekends?

Win7, Excel 2010

Thanks in advance for any help, let me know if you need more info!

gexexcel

 

[AngelEG]

Hi gexexcel,

One way of doing it is by adding some additional columns like this:

Calculating the time span between two dates:
Cell D2 has formula =DATE(YEAR(C2),MONTH(C2),1)
Cell E2 has formula =NETWORKDAYS(B2,C2,HolidayTable)

Cell D2 has formula =DATE(YEAR(F2),MONTH(F2),1)
Cell E2 has formula =NETWORKDAYS(C2,F2,HolidayTable)

Calculating the average time according to the month of service:
Cell L2 has formula =IFERROR(AVERAGEIF($D2:$D11,L$1,$E2:$E11),0)
Cell L3 has formula =IFERROR(AVERAGEIF($G2:$G11,L$1,$H2:$H11),0)

The formula NETOWRKDAYS calculates difference between 2 dates excluding weekends and holidays (holidays need to be defined in a range or table, in the example above I just created a table for easy reference); the formula counts the initial date as the first day so it will give you 4 days from 11/2/15 to 11/5/15, if you don't like this just add a -1 to the end of the formula.

The AVERAGEIF calculates the average of a given range only if a condition is met in the range, in this case I'm using the header (Nov-15) as a date (11/1/15), this is the condition, so it will only average the working days where the "Month-Year" matches the Measure date.

I've uploaded the working file above if you want to review the formulas, this is the link -> https://drive.google.com/file/d/0B1t6R5UwL0-2ZGNjX2k5QnJfdnM/view?usp=sharing

Cheers,

Angel

 

[gexexcel]

Looks good! Thanks for the help!

 

[AngelEG]

Glad I could help.

Looks good! Thanks for the help!