Average completion times..

[megaman777]

Hello, hope someone can help- i need 2 formulae, for the following scenarios. The spreadsheet currently has about 200 rows of data, but new rows are added to it all the time.

1. Average time to complete
- where column A is the start date
- column B is the completion date (column B must have a Date value and not 'TBD')

how do i work out the average completion time in days, across all of the rows in the worksheet?

2. Average time open (not completed)
- where column A is the start date
- column B contains 'TBD' and therefore todays date should be used to calculate the 'time open'

how do i workout the average 'time open', across all of the rows in the worksheet?

 

[johnny51981]

Would something like this work out for you, adding 2 columns to calculate the number of days Open or To Close for each row:

 

[megaman777]

Thank you this is really super helpful. One more question, is there something nifty you could do to result in a blank value where the field values in both columns are n/a? Or easier just to omit the formula from those rows where that's the case?

 

[awoohaw]

How about something like this:

mr excel questions 15.xlsm
	A	B	C	D	E
1	Start	Finsh		Current Date:	2023-03-21
2	2023-03-11	2023-03-17		Average Time Closed	4.90909091
3	2023-03-14	2023-03-19		Average Time Open	7
4	2023-03-13
5	2023-03-22	2023-03-28
6	2023-03-15	2023-03-18
7	2023-03-10	2023-03-15
8	2023-03-16
9	2023-03-13
10	2023-03-22	2023-03-27
11	2023-03-11	2023-03-17
12	2023-03-13	2023-03-19
13	2023-03-14
14	2023-03-13	2023-03-16
15	2023-03-11	2023-03-15
16	2023-03-21	2023-03-26
17
Megaman777

Cell Formulas
Range	Formula
E2	E2	=AVERAGE(FILTER($B$2:$B$16,ISNUMBER($B$2:$B$16),"")-FILTER($A$2:$A$16,ISNUMBER($B$2:$B$16),""))
E3	E3	=AVERAGE($E$1-FILTER($A$2:$A$16,$B$2:$B$16="",""))