Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional formatting for cell date to equal today's date | Excel Worksheet Functions | |||
I need today's date returned as date format in formula | Excel Discussion (Misc queries) | |||
Count number of cells with date <today's date | New Users to Excel | |||
count the number of cells within a column with a date <= today's d | New Users to Excel | |||
count the number of cells with a date <= today's date | New Users to Excel |