Count cells within a future date range

[asharp22]

Hello,

I am new to creating functions in Excel and I need to count the number of cells within a column that have a date range for this month and then in another cell I need to count the number of cells in a column that are for next month.

Not sure if I'm making sense but I am trying to make a formula that counts license plate due dates. I have a large column full of the dates that the license plates need to be renewed. So I need to know how many have to be renewed within 30 days or less, and another formula to know if they are due in 31-60 days. I've scoured the internet to find a formula that does this. I need the count to change daily, as the date changes, and I need the count to reflect that change.

Please let me know if this is possible.

I am using Excel 2003 (my work does not use a newer version)

thanks!!

[DGagnon]

sorry, that was incorrect, try this, it will compensate for years going over.

=SUMPRODUCT(--(A1:A1000>=DATE(YEAR(TODAY()),MONTH(TODAY()),1)),--(A1:A1000>=DATE(YEAR(TODAY())+IF(MONTH(TODAY())=12,1,0),IF(MONTH(TODAY())=12,1,MONTH(TODAY())+1),1)))

=SUMPRODUCT(--(A1:A1000>=DATE(YEAR(TODAY())+IF(MONTH(TODAY())=12,1,0),IF(MONTH(TODAY())=12,1,MONTH(TODAY())+1),1)),--(A1:A1000>=DATE(YEAR(TODAY())+IF(OR(MONTH(TODAY())=12,MONTH(TODAY())=11),1,0),IF(MONTH(TODAY())=12,2,IF(MONTH(TODAY())=11,1,MONTH(TODAY())+1)),1)))

[Haseeb Avarakkan]

Consider Column A contains the dates.

In C1 enter

=TODAY()-DAY(TODAY())+1 <-- This will give he starting of the current month

In D1 enter,

=DATE(YEAR(C1),MONTH(C1)+1,0) <-- This will give the end of he current month.

Then use for the current month count,

=COUNTIF(A:A,">="&C1)-COUNTIF(A:A,">"&D1)

Next month count,

=COUNTIF(A:A,">"&D1)-COUNTIF(A:A,">"&DATE(YEAR(C1),MONTH(C1)+2,0))

[DGagnon]

that would not account for such as December, it would return a month of 13 for end of month, or 14 for end of next month.

[vlady]

hello,
i'm not really giving formula now.. just the logic above, i think

how many days/months before renewal of license plate so :

date of license + days/month before renew = total date
march 6, 2012 + 30 days before renew = total date

if total date < todays date = count it

I need the count to change daily,

and another one ( come your formulas above )

if total date >31 <60 = count it

[Haseeb Avarakkan]

Excel will convert to suitable If month/Day is <>.

eg:

=DATE(2012,13,1) will return 1/Jan/2013

=DATE(2012,14,1) will return 1/Feb/2013

=DATE(2012,14,31) will return 3/Mar/2013

Excel help says,

If month is greater than 12, month adds that number of months to the first month in the year specified
If month is less than 1, month subtracts the magnitude of that number of months, plus 1, from the first month in the year specified

If day is greater than the number of days in the month specified, day adds that number of days to the first day in the month
If day is less than 1, day subtracts the magnitude that number of days, plus one, from the first day of the month specified

[DGagnon]

[QUOTE=Haseeb A;2726193]Excel will convert to suitable If month/Day is <>.QUOTE]

I am mistaken, i did not think it would give this result, but after some further checking and your above post it does appear to do so. thank you for clearing this up.

[asharp22]

Consider Column A contains the dates.

In C1 enter

=TODAY()-DAY(TODAY())+1 <-- This will give he starting of the current month

In D1 enter,

=DATE(YEAR(C1),MONTH(C1)+1,0) <-- This will give the end of he current month.

Then use for the current month count,

=COUNTIF(A:A,">="&C1)-COUNTIF(A:A,">"&D1)

Next month count,

=COUNTIF(A:A,">"&D1)-COUNTIF(A:A,">"&DATE(YEAR(C1),MONTH(C1)+2,0))

This worked to get the beginning date of this month, and the ending date of this month, but the next two formulas did not work. The cells I'm using to count have a range of P11:P500, and I'm trying to have the sum of the next 30 days appear in cell A5, and the sum of the next 30 days in A6. I think it's mostly Excel 2003 that is tripping me up.

Thanks for the help, I have tried all the other formulas suggested here as well, and none of them worked, even when I changed the cell range to what I needed it to be.

Any further help, again, would be greatly appreciated.

[DGagnon]

in A5 use:

=SUMPRODUCT(--(P11:P500>=TODAY()-DAY(TODAY())+1),--(P11:P500<DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)))

in A6 use:

=SUMPRODUCT(--(P11:P500>=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)),--(P11:P500<DATE(YEAR(TODAY()),MONTH(TODAY())+2,1)))

[asharp22]

in A5 use:

=SUMPRODUCT(--(P11:P500>=TODAY()-DAY(TODAY())+1),--(P11:P500<DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)))

in A6 use:

=SUMPRODUCT(--(P11:P500>=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)),--(P11:P500<DATE(YEAR(TODAY()),MONTH(TODAY())+2,1)))

Perfect!!!! Thank you so much!!

[Haseeb Avarakkan]

...but the next two formulas did not work...

C1 & d1 is just the helpers to get Start & End date of the current month

you could also use without helpers,

=SUMPRODUCT((TEXT(P11:P500,"mmm-yyyy")=TEXT(TODAY()-DAY(TODAY())+1,"mmm-yyyy"))+0)

=SUMPRODUCT((TEXT(P11:P500,"mmm-yyyy")=TEXT(TODAY()-DAY(TODAY())+32,"mmm-yyyy"))+0)