Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting dates for a the present month but not future months
How do I count the number of dates found in an array of dates, for a given
month. In addition, I do not wish to count any dates that occur in future months. Right now my formula looks like this, where "my_dates" is a range on another worksheet containing dates, and "given_mo_yr" is a given Month/Year date. {COUNT(IF(YEAR(my_date)=YEAR(given_mo_yr),IF(MONTH (my_date)=MONTH(given_mo_yr),IF(MONTH(my_date)<=MO NTH(NOW()),my_date))))} The problem with this is that it counts dates that occur in the future, and my goal is to not count those dates until it is that month. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting dates for a the present month but not future months
=sumproduct(--(text(a1:a10,"yyyymm")="200512")
is one way to count the dates in December of 2005. BrianInCalifornia wrote: How do I count the number of dates found in an array of dates, for a given month. In addition, I do not wish to count any dates that occur in future months. Right now my formula looks like this, where "my_dates" is a range on another worksheet containing dates, and "given_mo_yr" is a given Month/Year date. {COUNT(IF(YEAR(my_date)=YEAR(given_mo_yr),IF(MONTH (my_date)=MONTH(given_mo_yr),IF(MONTH(my_date)<=MO NTH(NOW()),my_date))))} The problem with this is that it counts dates that occur in the future, and my goal is to not count those dates until it is that month. -- Dave Peterson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting dates for a the present month but not future months
Dave, Thanks for your reply. Your method is much more compact than the one I
was useing. However, I'm still having problems only returning a number for dates less than or equal to the current date. For example, if my list of dates had November, December and January dates, and it was currently December, I only want to count November and December dates in thier respective cells and the January cell would be forced to 0 regardless of how many January dates are in the list. Here's what I tried to do: =IF(MONTH(my_date) <= MONTH(NOW()),SUMPRODUCT(--(TEXT(my_date,"mmm-yy")="Jan-06")), 0) Thanks!! "Dave Peterson" wrote: =sumproduct(--(text(a1:a10,"yyyymm")="200512") is one way to count the dates in December of 2005. BrianInCalifornia wrote: How do I count the number of dates found in an array of dates, for a given month. In addition, I do not wish to count any dates that occur in future months. Right now my formula looks like this, where "my_dates" is a range on another worksheet containing dates, and "given_mo_yr" is a given Month/Year date. {COUNT(IF(YEAR(my_date)=YEAR(given_mo_yr),IF(MONTH (my_date)=MONTH(given_mo_yr),IF(MONTH(my_date)<=MO NTH(NOW()),my_date))))} The problem with this is that it counts dates that occur in the future, and my goal is to not count those dates until it is that month. -- Dave Peterson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting dates for a the present month but not future months
I'm not sure how far back you go, but maybe...
=sumproduct(--(a1:a10=date(2005,11,01)),--(a1:a10<=today())) (for through today) Or through the end of the current month: =SUMPRODUCT(--(A1:A10=DATE(2005,11,1)), --(A1:A10<=DATE(YEAR(TODAY()),MONTH(TODAY())+1,0))) (the 0th of any month is the last day of the previous month) BrianInCalifornia wrote: Dave, Thanks for your reply. Your method is much more compact than the one I was useing. However, I'm still having problems only returning a number for dates less than or equal to the current date. For example, if my list of dates had November, December and January dates, and it was currently December, I only want to count November and December dates in thier respective cells and the January cell would be forced to 0 regardless of how many January dates are in the list. Here's what I tried to do: =IF(MONTH(my_date) <= MONTH(NOW()),SUMPRODUCT(--(TEXT(my_date,"mmm-yy")="Jan-06")), 0) Thanks!! "Dave Peterson" wrote: =sumproduct(--(text(a1:a10,"yyyymm")="200512") is one way to count the dates in December of 2005. BrianInCalifornia wrote: How do I count the number of dates found in an array of dates, for a given month. In addition, I do not wish to count any dates that occur in future months. Right now my formula looks like this, where "my_dates" is a range on another worksheet containing dates, and "given_mo_yr" is a given Month/Year date. {COUNT(IF(YEAR(my_date)=YEAR(given_mo_yr),IF(MONTH (my_date)=MONTH(given_mo_yr),IF(MONTH(my_date)<=MO NTH(NOW()),my_date))))} The problem with this is that it counts dates that occur in the future, and my goal is to not count those dates until it is that month. -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dates - Months & Years | Excel Worksheet Functions | |||
Working days left in the month compared to previous months | Excel Worksheet Functions | |||
months between 2 dates!!! | Excel Discussion (Misc queries) | |||
Counting months | Excel Worksheet Functions | |||
Need More Help on Dates to Months | Excel Worksheet Functions |