Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 23
Default 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)
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 913
Default 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
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,480
Default 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)


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 23
Default 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)


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 23
Default 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



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 913
Default 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

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 913
Default 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


  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,718
Default 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)

  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,480
Default 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)


  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 23
Default 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)


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
conditional formatting for cell date to equal today's date Sistereinstein Excel Worksheet Functions 2 September 10th 12 07:53 PM
I need today's date returned as date format in formula CMIConnie Excel Discussion (Misc queries) 2 February 23rd 06 05:38 PM
Count number of cells with date <today's date Cachod1 New Users to Excel 2 January 28th 06 03:37 AM
count the number of cells within a column with a date <= today's d Cachod1 New Users to Excel 2 January 28th 06 12:24 AM
count the number of cells with a date <= today's date Cachod1 New Users to Excel 3 January 27th 06 10:14 PM


All times are GMT +1. The time now is 12:48 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"