C D E
1 August 2, 2004 August 6, 2004 4
2 August 6, 2004 November 9, 2005 460
3 August 6, 2004 565
4 August 20, 2004 June 13, 2005 297
5 August 30, 2004 October 19, 2004 50
6 September 7, 2004 September 27, 2004 20
I have open dates in C and close dates in D and I have the formula
{=MAX(IF(ISBLANK(D52),TODAY(),D52)-C52,1)} in Column E that gives me the
number of days the file was open.
These dates begin in 09/2003 until present and are added onto daily. I need
to AVERAGE the number of days open (D:D-C:C) for each quarter of each fiscal
year. {=AVERAGE(IF(C1:C500="10/01/2003"<="12/31/2003"),D1:D500-C1:C500)} but
it would definitely be easier if I could instead use
{=AVERAGE(IF(INT((MONTH(C1:C500)+2)/3)=4,D1:D500-C1:C500))} however, I don't
understand how to get results for each individual 1st QTR FY04, 2nd QTR FY04,
3rd QTR FY04, 4th QTR FY04, 1st QTR FY05, 2nd QTR FY05, 3rd QTR FY05, 4th QTR
FY05, 1st QTR FY06, 2nd QTR FY06, etc...