Trying to work out the average number between dates with others (found using a vlookup)

Hi all,

I've got a bit of a challenge I can't work out.

I have a list of dates in column A (Date Notified) and in column B a list of corresponding periods (Period Resolved), on a separate tab I have the date ranges for the periods (Start Date, End Date and Period)

I need to work out the average number of days between the Date Notified and the End date of the Period Resolved, and I cannot figure it out. If it was just two lists of dates then I could use

Formula:

Please Login or Register  to view this content.

, but I need to factor in a lookup/index match I think to find out when each instance was resolved.

I've attached a sample worksheet showing the layout.

Any help would be appreciated.

Thanks

David

Hi humdingaling,

07/2014 is actually the pay period in which the instance was resolved, so 07/2014 started on 8/09/2014 and finished on 21/09/2014. The date I'm looking to find the average from in that instance would be the 1/8/14 (date notified) and 21/9/14 (the end date in the period 07/2014).

I hope that makes sense.

Thanks

Hi humdingaling,

Unfortunately a helper column isn't feasible in this instance.

Any help would be appreciated.

Hi humdingaling,

Thanks for the solution, I've tried implementing it but I've come across an issue.

The list of periods that I use goes all the way back to 2010, so I'll have 07/2010, 07/2011, 07/2012 etc.

The lookup formula is now picking up one of the earlier similar periods, not looking for an exact match and this is throwing the figure way out.

Any idea how I could incorporate a Vlookup or index match into the formula instead? I've had a play but to no avail.

Thanks

David