How to average time between dates
Requirement:
The user has a table of three columns (please see below).
The first is a list of Work Order Numbers.
The second is a list of Asset Numbers that the Work Orders are assigned to.
The third is the Report Date, which is a list of dates the Work Orders were raised.
The user wants to work out the average time between Report dates for each asset. i.e. Asset A has an average of XX days between Work Orders, Asset B has an average of XX days between Work Orders etc.
| Work Order | Asset | Report Date |
| 100001 | A | 01-Apr-18 |
| 100002 | A | 04-Apr-18 |
| 100003 | A | 10-Apr-18 |
| 100004 | A | 20-Apr-18 |
| 100005 | B | 02-Apr-18 |
| 100006 | B | 10-Apr-18 |
| 100007 | B | 16-Apr-18 |
| 100008 | B | 20-Apr-18 |
| 100009 | B | 25-Apr-18 |
| 100010 | B | 30-Apr-18 |
| 100011 | C | 01-Apr-18 |
| 100012 | C | 05-Apr-18 |
| 100013 | C | 15-Apr-18 |
| 100014 | C | 22-Apr-18 |
| 100015 | C | 28-Apr-18 |
| 100016 | C | 29-Apr-18 |
| 100017 | D | 05-Apr-18 |
| 100018 | D | 08-Apr-18 |
| 100019 | D | 19-Apr-18 |
| 100020 | D | 23-Apr-18 |
| 100021 | D |
29-Apr-18
|
Solution:
Assuming data above in A1:C22, try adding a helper column, in D1 add a title like "Days between", then in D2:
=IF(B2=B1,C2-C1,"")
copied down.
Then create a Pivot Table from the data and place Asset in Rows and "Days Between" in Summary, change the default of "Sum of...." to "Average of..."
Obtained from the OzGrid Help Forum.
Solution provided by NBVC.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to Average every certain # of columns |
| How to average both positive and negative numbers |
| How to average time between dates |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
Gallery
