# Can SS return an actual Date using Today formula?

✭✭✭✭✭✭

I'm using a simple formula to see if a Date falls within the next week, two weeks, three weeks, etc.

Can Smartsheet actually return the Date or Week that this falls in?

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

Appreciate all the brain power that's out there!

Jeana

• ✭✭✭✭✭

If I understand you correctly, you want a formula that returns a date that's 1, 2, 3, etc weeks out?

If that's the case, create a Date column and use the formula =TODAY(n) where n is the number of days out. 7 for 1 week, 14 for 2, etc.

Is that what you're looking for?

Cheers,

Ramzi

Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)

Feel free to email me: ramzi@cedartreeconsulting.com

• ✭✭✭✭✭✭

@Ramzi K Well, sort of. I have formula that will tell me IF a specific date is so many days/weeks in the future. What I'm looking for is the actual date to be returned. My current formula returns a 1 or 0 if the conditions are met. I want it to return the actual date - 10/20/2020 for example.

Thanks,

Jeana

• ✭✭✭✭✭

Got it. So one thing you can do is this:

=IF((Your Formula)=1, TODAY(([End Date]@row - TODAY()),"")

So basically it says that if your condition is true return Today's date + the number of days between today and the End Date.

This formula would need to go into a column of type Date

I hope this helps.

Cheers,

Ramzi

Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)

Feel free to email me: ramzi@cedartreeconsulting.com

• ✭✭✭✭✭✭

Thanks Ramzi but I'm still not getting a date returned. I'm not just counting the days on this one, I want to know what the date is when the formula says TODAY(+14)

Jeana

• ✭✭✭✭✭

Jeana, it may be best if you shared a screenshot or perhaps the sheet itself (to ramzi@cedartreeconsulting.com). It would give me a better idea of what you are trying to do.

Cheers,

Ramzi

Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)

Feel free to email me: ramzi@cedartreeconsulting.com

• ✭✭✭✭✭✭

Ramzi, I was typing out the logic to better explain what I'm looking for when it hit me!!! I HAVE the date I'm looking for, I just need to collect the range. So here's my new question.

I want the formula to determine if a date range (CHILDREN([End Date]:[End Date]) is in the next 7 days. If it is then return the MIN of the date range.

I'm trying this formula to do it and it's not quite working.

Thank you!

• ✭✭✭✭✭✭
edited 10/13/20

I think I almost have it. This formula returns Invalid Data Type???? The column is set as a Date column.

=IF(CHILDREN([Next Week]@row:[Next Week]@row), =1, MAX(CHILDREN([End Date]@row:[End Date]@row)))

• ✭✭✭✭✭✭

Just what I was looking for! Thank you so much!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!