How do count blank cells between some period of time?
This way stops the need to enter the date in the formula
If E1:f4 contains
Start_Date N Days
21/09/2009 6
Replies Left
4
The formula in E1 refers to the date in E2 and the number f days to inspect
in F2.
=(SUMPRODUCT(--(Received_Dates=E2))-SUMPRODUCT(--(Received_Dates=E2+F2)))-(SUMPRODUCT(--(Reply_Dates=E2))-SUMPRODUCT(--(Reply_Dates=E2+F2)))
With dynamic range names for received and reply dates.
Received dates =OFFSET(Sheet6!$B$2,0,0,COUNTA(Sheet6!$B:$B)-1)
Reply Dates =OFFSET(Received_Dates,0,1)
You will need to change the Sheet name on with the ranges occur for the
first name.
To insert a dynamic range name:
in xl2003
Choose Insert, Name, Define. In the Names in Workbook text box type your name.
In the Refers to Paste the formula
Click Add
Type the Second range name in the 'Names in Workbook' text box
Type or paste the 2nd Formula
Click OK
In xl2007
Select the Formulas Tab
choose Name Manager
Click New
and follow the steps above.
HTH
Peter Atherton
"Jul" wrote:
I need to find quantity of Letters between some period of time (one week)
from 21-09-09 until 27-09-09, which are not replied (Cells with Reply date
are blank)
Letter No Received Date Reply Date
L-255 21-09-2009
L-256 23-09-2009 25-09-2009
L-257 25-09-2009 27-09-2009
L-258 21-09-2009
L-259 22-09-2009 25-09-2009
L-260 26-09-2009
|