how to count blanks before today's date?
 

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)

how to count blanks before today's date?
 

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

how to count blanks before today's date?
 

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)

how to count blanks before today's date?
 

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)

how to count blanks before today's date?
 

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

how to count blanks before today's date?
 

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

how to count blanks before today's date?
 

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

how to count blanks before today's date?
 

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)

how to count blanks before today's date?
 

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)

how to count blanks before today's date?
 

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)