COUNTIF cells that are due within 2 weeks from today's date

[rowena229]

I currently have a column with dates and I need to count how many dates are due within 2 weeks from today's date. The formula I have is =COUNTIF(Q11:Q1000,">="&(TODAY()+14))

Today's date: March 20, 2013

25-Jan-13 Overdue
27-Mar-13 Due in 2 weeks
2-Feb-13 Overdue
22-Mar-13 Due in 2 weeks
30-Mar-13 Due in 2 weeks
20-Mar-13 Due in 2 weeks
10-Jun-13
15-May-13

The answer that it is giving me is 2, but it should be 4.

Please help. I've been trying to figure this out for 2 days!

Thanks

[Spencer101]

The answer of 2 is correct for that formula as only two of the dates listed are greater than or equal to today's date +14 days.

Try:

=SUMPRODUCT(Q11:Q1000>=TODAY())*(Q11:Q1000<=TODAY()+14))

[jkj115]

you need to do countifs. Right now you are just counting numebr of dates the occur AFTER or EQUAL to Today+14. YOu need to find the number of days AFTER today and BEFORE 14 days from today

Please Login or Register  to view this content.

[Spencer101]

Note, you wont be able to use the COUNTIFS formula mentioned above if you're using Excel 2003 as your profile information sates.
(which is why I gave the SUMPRODUCT version).

[rowena229]

Thanks Spencer101, but would you know the formula to count all the days that are coming up within 2 weeks of today's date?

[rowena229]

I don't need to know 14 days before today's date. I just need to do a count of all dates that fall within 2 weeks of today's date.

So if today's date is March 20, 2013, it'll count all dates that are in between March 20-April 3.

[jkj115]

you can just add a column next to each date and put a 1 in it, then use the sumproduct to to sum the number of "1"s

Please Login or Register  to view this content.

[rowena229]

Is there a way to do it without adding an additional column?

[rowena229]

This formula =SUMPRODUCT(Q11:Q1000>=TODAY())*(Q11:Q1000<=TODAY()+14)) did not work and gave me a a #VALUE!

[jkj115]

here you go

Please Login or Register  to view this content.

[jkj115]

that works I checked it. Its possible in your data you may not have it set up right? Are they all dates in the Q11:Q1000? Maybe attach the file?

[rowena229]

here you go

Please Login or Register  to view this content.

thank you

[rowena229]

here you go

Please Login or Register  to view this content.

thank you...it worked!!!

[jkj115]

No problem - please mark as solved and if I helped you click star under this post under my name!

[Spencer101]

Thanks Spencer101, but would you know the formula to count all the days that are coming up within 2 weeks of today's date?

That's what the SUMPRODUCT formula I provided does.

=SUMPRODUCT((Q11:Q1000>=TODAY())*(Q11:Q1000<=TODAY()+14))

Will count dates in column Q that are greater than or equal to today's date AND less than or equal to the date 14 days from now.

[Spencer101]

This formula =SUMPRODUCT(Q11:Q1000>=TODAY())*(Q11:Q1000<=TODAY()+14)) did not work and gave me a a #VALUE!

Sorry, it was missing an opening bracket after SUMPRODUCT. MY bad.

[jkj115]

Spencer - your original formula was missing parenthesis in beginning

[Spencer101]

Spencer - your original formula was missing parenthesis in beginning

Thanks, I noticed that. I blame this iPad keyboard!

[Spencer101]

Spencer - your original formula was missing parenthesis in beginning

Thanks, I noticed that. I blame this iPad keyboard!