how to count blanks before today's date?

[MAANI]

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)

 

[Lars-Åke Aspelin]

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

 

[Roger Govier]

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.

 

[MAANI]

Roger..didnt work,,it returns 0

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.

 

[MAANI]

Returned zero

Try this formula:

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

Hope this helps / Lars-Ã…ke

 

[Lars-Åke Aspelin]

Try this formula:

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

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

 

[Lars-Åke Aspelin]

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

 

[Teethless mama]

Try this:

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

 

[Roger Govier]

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

 

[MAANI]

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