COUNTIF Date is in the next 180 days from today

I'm trying to use the COUNTIF function to search a date column to count how many dates are within the next 180 days from today...but my formulas aren't working correctly.

Failed Attempt 1: =COUNTIF([Renewal Date]2, >=TODAY(+180))

Failed Attempt 2: =COUNTIF([Renewal Date]2, >=TODAY()180)

Failed Attempt 3: =COUNTIF([Renewal Date]2, AND(@cell >= TODAY(), @cell > TODAY(+180)))

What am I doing wrong?

Find more posts tagged with

Formulas

Comments

Bryan Sampson

Try this and see if it works:

COUNTIF(Renewal Date1:Renewal Date402, >(TODAY() + 180))

Update the Renewal Date1:Renewal Date 402 with the appropriate first and last cell in the column to be looked at

The formula I am basing this off of is COUNTIF(Finish1:Finish402, >(TODAY() + 10)) which is looking for items in the next 10 days and it works for me.

Darcie Wilson

This formula isn't calculating correctly. It's calculating 3, but there are only 2 dates that are within the next 180 days. See attachment

Mike Wilday

I think you might actually want all dates that are less than Today + 180... so within the next 180 days.

=COUNTIF(Date1, <=TODAY(180))

This one worked for me. But if you don't want it to count items that are in the past you would also want to edit it to the following.

=COUNTIFS(Date1, <=TODAY(180), Date1, >=TODAY())

Mike Wilday

Sorry - edited for your current instance

=COUNTIF([Renewal Date]2, <=TODAY(180))

This one worked for me. But if you don't want it to count items that are in the past you would also want to edit it to the following.

=COUNTIFS([Renewal Date]2, <=TODAY(180), [Renewal Date]2, >=TODAY())

Sign in to join the conversation!

Quick Links

Trending discussions

Calculate number of days between two dates in the same column on the same sheet
Hello, Can a combination of the INDEX(COLLECT and NETDAYS Formula be used to get the number of days between two dates in the same column? I want to get the number of days between two dates on the same sheet, in the same column, that have matching names and different project types. For Example: Studio Name is equal to…
Match or Index
I am trying to pull a phone number and email from our master roster that is on another sheet in Smartsheet. I cannot figure out the formula. This is the formula I have tried. =INDEX([EMPLOYEE NAME]@row , MATCH({IMS Master Roster Range 2}), 0)
Workflow Generate Impact Report PDF in Impact Form Response Sheet has become invalid
Hi All, I keep getting this annoying error when I try to trigger a PDF generation. I initially set up to trigger the PDF generation to kick off once the last field the needs data is "not blank". That is when the error started. I modified the sheet and added a yes/no drop down to trigger the PDF generation when the value in…