C2 to IK2 have dates from 1-Aug-08 to 31-Mar-09, B4 to B159 eployee names,I
want a formula to count blanks before todays date,Example:if today is
4-Aug-08, I want the formula to return 312 which is countblank(C4:E159)

On Sat, 13 Sep 2008 00:26:01 -0700, MAANI
wrote:

C2 to IK2 have dates from 1-Aug-08 to 31-Mar-09, B4 to B159 eployee names,I
want a formula to count blanks before todays date,Example:if today is
4-Aug-08, I want the formula to return 312 which is countblank(C4:E159)


Try this formula:

=COUNTBLANK(OFFSET(C4:C159,,,,MATCH(TODAY(),C2:IK2 )))

Hope this helps / Lars-Åke

Returned zero

"Lars-Ã…ke Aspelin" wrote:

On Sat, 13 Sep 2008 00:26:01 -0700, MAANI
wrote:

C2 to IK2 have dates from 1-Aug-08 to 31-Mar-09, B4 to B159 eployee names,I
want a formula to count blanks before todays date,Example:if today is
4-Aug-08, I want the formula to return 312 which is countblank(C4:E159)


Try this formula:

=COUNTBLANK(OFFSET(C4:C159,,,,MATCH(TODAY(),C2:IK2 )))

Hope this helps / Lars-Ã…ke

The formula expects the dates to be "numbers formatted as date"a nd
not "text". Maybe that is why you don't get the expected result.

Lars-Åke


On Sat, 13 Sep 2008 04:49:01 -0700, MAANI
wrote:

Returned zero

"Lars-Åke Aspelin" wrote:

On Sat, 13 Sep 2008 00:26:01 -0700, MAANI
wrote:

C2 to IK2 have dates from 1-Aug-08 to 31-Mar-09, B4 to B159 eployee names,I
want a formula to count blanks before todays date,Example:if today is
4-Aug-08, I want the formula to return 312 which is countblank(C4:E159)


Try this formula:

=COUNTBLANK(OFFSET(C4:C159,,,,MATCH(TODAY(),C2:IK2 )))

Hope this helps / Lars-Åke

On Sat, 13 Sep 2008 07:56:41 GMT, Lars-Åke Aspelin
wrote:

On Sat, 13 Sep 2008 00:26:01 -0700, MAANI
wrote:

C2 to IK2 have dates from 1-Aug-08 to 31-Mar-09, B4 to B159 eployee names,I
want a formula to count blanks before todays date,Example:if today is
4-Aug-08, I want the formula to return 312 which is countblank(C4:E159)


Try this formula:

=COUNTBLANK(OFFSET(C4:C159,,,,MATCH(TODAY(),C2:IK 2)))

Hope this helps / Lars-Åke

Actually the formula I gave included todays data.
In order to have only the blanks BEFORE todays data, a -1 should be
included, like this:

=COUNTBLANK(OFFSET(C4:C159,,,,MATCH(TODAY(),C2:IK2 )-1))

Lars-Åke

Hi

Try
=COUNTBLANK($C$4:INDEX($C$4:$IK$159,156,MATCH($A$1-1,$C$2:$IK$2,0)))

I used cell A1 to hold the date, into which I entered 04 Aug 08 (as Today()
is past that date).
You can either put =TODAY() in cell A1, or substitute Today() in place of
$A$1 in the formula.

Incidentally, I make the answer 468, not 312.
There are three columns of 156 rows for 1/8, 2/8 and 3/8 hence 468 blanks.

--
Regards
Roger Govier

"MAANI" wrote in message
...
C2 to IK2 have dates from 1-Aug-08 to 31-Mar-09, B4 to B159 eployee
names,I
want a formula to count blanks before todays date,Example:if today is
4-Aug-08, I want the formula to return 312 which is countblank(C4:E159)

Roger..didnt work,,it returns 0

"Roger Govier" wrote:

Hi

Try
=COUNTBLANK($C$4:INDEX($C$4:$IK$159,156,MATCH($A$1-1,$C$2:$IK$2,0)))

I used cell A1 to hold the date, into which I entered 04 Aug 08 (as Today()
is past that date).
You can either put =TODAY() in cell A1, or substitute Today() in place of
$A$1 in the formula.

Incidentally, I make the answer 468, not 312.
There are three columns of 156 rows for 1/8, 2/8 and 3/8 hence 468 blanks.

--
Regards
Roger Govier

"MAANI" wrote in message
...
C2 to IK2 have dates from 1-Aug-08 to 31-Mar-09, B4 to B159 eployee
names,I
want a formula to count blanks before todays date,Example:if today is
4-Aug-08, I want the formula to return 312 which is countblank(C4:E159)

Provided you have true excel dates in C2:IK2 it works fine.
As I said it returns a result for me of 468

--
Regards
Roger Govier

"MAANI" wrote in message
...
Roger..didnt work,,it returns 0

"Roger Govier" wrote:

Hi

Try
=COUNTBLANK($C$4:INDEX($C$4:$IK$159,156,MATCH($A$1-1,$C$2:$IK$2,0)))

I used cell A1 to hold the date, into which I entered 04 Aug 08 (as
Today()
is past that date).
You can either put =TODAY() in cell A1, or substitute Today() in place of
$A$1 in the formula.

Incidentally, I make the answer 468, not 312.
There are three columns of 156 rows for 1/8, 2/8 and 3/8 hence 468
blanks.

--
Regards
Roger Govier

"MAANI" wrote in message
...
C2 to IK2 have dates from 1-Aug-08 to 31-Mar-09, B4 to B159 eployee
names,I
want a formula to count blanks before todays date,Example:if today is
4-Aug-08, I want the formula to return 312 which is countblank(C4:E159)

Roger,Lars thank you very much,Roger I used your formula it works perfect but
it should be in the last column,thanks again.This community rules

"Roger Govier" wrote:

Hi

Try
=COUNTBLANK($C$4:INDEX($C$4:$IK$159,156,MATCH($A$1-1,$C$2:$IK$2,0)))

I used cell A1 to hold the date, into which I entered 04 Aug 08 (as Today()
is past that date).
You can either put =TODAY() in cell A1, or substitute Today() in place of
$A$1 in the formula.

Incidentally, I make the answer 468, not 312.
There are three columns of 156 rows for 1/8, 2/8 and 3/8 hence 468 blanks.

--
Regards
Roger Govier

"MAANI" wrote in message
...
C2 to IK2 have dates from 1-Aug-08 to 31-Mar-09, B4 to B159 eployee
names,I
want a formula to count blanks before todays date,Example:if today is
4-Aug-08, I want the formula to return 312 which is countblank(C4:E159)

Try this:

=SUMPRODUCT((C2:IK2<TODAY())*(C4:IK159=""))


"MAANI" wrote:

C2 to IK2 have dates from 1-Aug-08 to 31-Mar-09, B4 to B159 eployee names,I
want a formula to count blanks before todays date,Example:if today is
4-Aug-08, I want the formula to return 312 which is countblank(C4:E159)