Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Count
I must be going senile, as I can usually crack these things, but I am having
massive problems counting dates. In column A I have the date entered as follows: =TEXT(WORKDAY(A636,1),"dd-mmm-yy") I need to count every date where the year is equal to the current year. Please put me out of my misery and I apologise for such a dumb ass question. Richard |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Count
Try
=SUMPRODUCT(--(YEAR($A$1:$A$100)=YEAR(TODAY()))) Mike "Richard" wrote: I must be going senile, as I can usually crack these things, but I am having massive problems counting dates. In column A I have the date entered as follows: =TEXT(WORKDAY(A636,1),"dd-mmm-yy") I need to count every date where the year is equal to the current year. Please put me out of my misery and I apologise for such a dumb ass question. Richard |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Count
Mike
Thanks for the reply. I found the issue was that I had entered =sumproduct(--(year(a:a)=year(today()))) with there being a lable (column header) in cell a2. I kept getting a return of #NUM! Is there a way around the label issue. Richard "Mike H" wrote: Try =SUMPRODUCT(--(YEAR($A$1:$A$100)=YEAR(TODAY()))) Mike "Richard" wrote: I must be going senile, as I can usually crack these things, but I am having massive problems counting dates. In column A I have the date entered as follows: =TEXT(WORKDAY(A636,1),"dd-mmm-yy") I need to count every date where the year is equal to the current year. Please put me out of my misery and I apologise for such a dumb ass question. Richard |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Count
Hi,
=SUMPRODUCT(--(YEAR(A2:A65536)=YEAR(TODAY()))) Mike "Richard" wrote: Mike Thanks for the reply. I found the issue was that I had entered =sumproduct(--(year(a:a)=year(today()))) with there being a lable (column header) in cell a2. I kept getting a return of #NUM! Is there a way around the label issue. Richard "Mike H" wrote: Try =SUMPRODUCT(--(YEAR($A$1:$A$100)=YEAR(TODAY()))) Mike "Richard" wrote: I must be going senile, as I can usually crack these things, but I am having massive problems counting dates. In column A I have the date entered as follows: =TEXT(WORKDAY(A636,1),"dd-mmm-yy") I need to count every date where the year is equal to the current year. Please put me out of my misery and I apologise for such a dumb ass question. Richard |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Count
I forgot to mention Sumproduct will not accept A:A as a range
"Richard" wrote: Mike Thanks for the reply. I found the issue was that I had entered =sumproduct(--(year(a:a)=year(today()))) with there being a lable (column header) in cell a2. I kept getting a return of #NUM! Is there a way around the label issue. Richard "Mike H" wrote: Try =SUMPRODUCT(--(YEAR($A$1:$A$100)=YEAR(TODAY()))) Mike "Richard" wrote: I must be going senile, as I can usually crack these things, but I am having massive problems counting dates. In column A I have the date entered as follows: =TEXT(WORKDAY(A636,1),"dd-mmm-yy") I need to count every date where the year is equal to the current year. Please put me out of my misery and I apologise for such a dumb ass question. Richard |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Count
On Thu, 19 Jun 2008 04:15:02 -0700, Richard
wrote: Mike Thanks for the reply. I found the issue was that I had entered =sumproduct(--(year(a:a)=year(today()))) with there being a lable (column header) in cell a2. I kept getting a return of #NUM! Is there a way around the label issue. Richard I'm surprised you are getting a #NUM! error and not a #VALUE! error. Since you have specified an entire column as your argument, I am assuming you are using Excel 2007. That being the case, you can try this **array** formula: =SUM(--(IF(ISNUMBER(--A:A),YEAR(A:A))=YEAR(TODAY()))) To enter an array formula, hold down <ctrl<shift while hitting <enter. Excel will place braces {...} around the formula. If you are not using Excel 2007, then your arguments must not encompass an entire column. e.g: =SUM(--(IF(ISNUMBER(--A1:A65535),YEAR(A1:A65535))=YEAR(TODAY()))) --ron |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Count
No, I am not using 2007, I just tried a:a sorry it is lack of sleep.
at least I understand now thanks for the help "Ron Rosenfeld" wrote: On Thu, 19 Jun 2008 04:15:02 -0700, Richard wrote: Mike Thanks for the reply. I found the issue was that I had entered =sumproduct(--(year(a:a)=year(today()))) with there being a lable (column header) in cell a2. I kept getting a return of #NUM! Is there a way around the label issue. Richard I'm surprised you are getting a #NUM! error and not a #VALUE! error. Since you have specified an entire column as your argument, I am assuming you are using Excel 2007. That being the case, you can try this **array** formula: =SUM(--(IF(ISNUMBER(--A:A),YEAR(A:A))=YEAR(TODAY()))) To enter an array formula, hold down <ctrl<shift while hitting <enter. Excel will place braces {...} around the formula. If you are not using Excel 2007, then your arguments must not encompass an entire column. e.g: =SUM(--(IF(ISNUMBER(--A1:A65535),YEAR(A1:A65535))=YEAR(TODAY()))) --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I count a record if it contains a date within a date range | Excel Worksheet Functions | |||
count the mean in few date but on de date it has various data | Excel Worksheet Functions | |||
count between start date and end date | Excel Discussion (Misc queries) | |||
Count number of cells with date <today's date | New Users to Excel | |||
count the number of cells with a date <= today's date | New Users to Excel |