Dear All,
I am having issues with this formula:
=IF(TODAY()<D10,"",IF(TODAY()<=D11,Register!$W$1,COUNTIFS(Register!$D$7:$D$3076,"<="&Record!D11,Register!$U$7:$U$3076,"")+COUNTIFS(Register!$D$7:$D$3076,"<="&Record!D11,Register!$U$7:$U$3076,">"&D11)-COUNTIFS(Register!$D$7:$D$3076,"<="&Record!D11,Register!$U$7:$U$3076,"<"&D10)))
I am trying to pull the number of open documents in the list between a set of given dates.
D10 = the 1st of a given month, e.g. 1/3/2013
D11 = the last date in a given month. e.g. 31/3/2013
Register!$W$1 = the current count of open documents listed
Register!$D:$D = Date document started/opened
Register!$U:$U = Date closed
So as I see it the formula above says:
1. If today if before the first date cell is blank
2. If today is before the last date cell equals the current count (and from this point it gets messy)
3. Count all rows where the document was opened before the last date and does not have a date closed (still open)
4. Plus Count all rows where the document was opened before the last date and was closed after the last date
5. minus Count all rows where the document was opened before the last date and was closed before the first date (closed before range)
The formula seems to work for last month but not for this month as the current count is 32 (and was at month end) but the cell reads 16. I don't know whether it's a problem with my formula or a step I have missed in the logic.......... Any help would be greatly appreciated,
Thank you for your time.
Kind Regards,
Marie
I am having issues with this formula:
=IF(TODAY()<D10,"",IF(TODAY()<=D11,Register!$W$1,COUNTIFS(Register!$D$7:$D$3076,"<="&Record!D11,Register!$U$7:$U$3076,"")+COUNTIFS(Register!$D$7:$D$3076,"<="&Record!D11,Register!$U$7:$U$3076,">"&D11)-COUNTIFS(Register!$D$7:$D$3076,"<="&Record!D11,Register!$U$7:$U$3076,"<"&D10)))
I am trying to pull the number of open documents in the list between a set of given dates.
D10 = the 1st of a given month, e.g. 1/3/2013
D11 = the last date in a given month. e.g. 31/3/2013
Register!$W$1 = the current count of open documents listed
Register!$D:$D = Date document started/opened
Register!$U:$U = Date closed
So as I see it the formula above says:
1. If today if before the first date cell is blank
2. If today is before the last date cell equals the current count (and from this point it gets messy)
3. Count all rows where the document was opened before the last date and does not have a date closed (still open)
4. Plus Count all rows where the document was opened before the last date and was closed after the last date
5. minus Count all rows where the document was opened before the last date and was closed before the first date (closed before range)
The formula seems to work for last month but not for this month as the current count is 32 (and was at month end) but the cell reads 16. I don't know whether it's a problem with my formula or a step I have missed in the logic.......... Any help would be greatly appreciated,
Thank you for your time.
Kind Regards,
Marie