CODEWITHSUNDEEP
excel-formula
Thomas Kuhlmann
Thomas Kuhlmann

Reputation: 325

Calculating the average difference in days within a period

I am trying to calculate the average difference between two columns in excel. The columns contain a planned and an actual date, I would like to get the average difference but only for planned dates within the last three months.

Example:

Planned Start Date | Actual Start Date

21/09/2013 | 25/09/2013
10/07/2014 | 16/07/2014
01/06/2014 | 30/06/2014

The formula should only take line 2 & 3 (line 1 is older than 3 months), look at the difference in days for each applicable line (line 2: 6 days, line 3: 29 days) and then show the average ( 17.5 days) of all applicable lines.

Does anybody have a formula for this? Excel really isn't my strong suit...

Upvotes: 0

Views: 459

Answers (1)

Alex P
Alex P

Reputation: 12489

Assume your data is in A2:B4 then try this array formula

=AVERAGE(IF(TODAY()-A2:A4<=90,B2:B4-A2:A4))
  • It assumes each month is 30 days (hence the 90)
  • Press CTRL + SHIFT + ENTER to enter the formula as an array formula

Based on your example I get a result of 17.5 days.

Upvotes: 2

Related Questions