How to add Dates to formula using TODAY(+7), etc.?
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
Answers

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)

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.

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 814 days ahead of the End Date. Otherwise it does return a 0.

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!
Help Article Resources
Categories
Check out the Formula Handbook template!