How to add Dates to formula using TODAY(+7), etc.?

Jeana

So I have several formulas calculating what date range a date is in...two weeks out, three weeks out, etc.

=COUNTIFS([Calc if Done]@row, =0, [End Date]@row, AND(@cell > TODAY(+7), @cell <= TODAY(+14)))

This give me a 1 or 0 result so I can count them, great! How can I change the formula so that I know what the DATES are for the Result? For example:

If End Date = Oct 12th, 2020

Formula above will return a 1. Can I get it to return the Date Range?

Oct 6th, 2020 - Oct 13th, 2020

Thanks so much for all the great advice I get in this community!!

Jeana

David Tutwiler

I think you'd want to change the COUNTIFS to an IF so you can determine what the output is. I'm thinking something along the lines of:

=IF(AND([Calc if Done]@row = 0, AND([End Date]@row > TODAY() +7, [End Date]@row <= TODAY() + 14)), TODAY() + 7, 0)

Jeana

Thanks for your reply David. However this didn't give me any different result. I'm still getting a 1 or 0 which I think makes sense for this formula. Still looking to return an actual date if possible.

David Tutwiler

That's odd, because the return would never be a 1 using that formula. I would've thought you'd either get a date, or an #INVALID COLUMN VALUE if you had the column set to receive text.

Have you tried changing the column's property to DATE?

Assuming your columns are set as described in the question, the formula I provided will return the date one week in advance of Today if the End Date is between 8-14 days ahead of the End Date. Otherwise it does return a 0.

Jeana

David,

I changed two things and we might be getting there. First the ,0 to a ,1 at the end of the formula - this counts the date range corrected. Then I changed the column format to date and I am now getting the #Date Expected error for that particular row. So, now I guess I need to figure out how to pull into the formula the End Date from that row.

If you have ideas on that I'd appreciate it. I'm not sure I"m following the formula you propose so I'm still a bit unsure as to how to do this.

Thanks so much!