Can SS return an actual Date using Today formula?

Jeana

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

Find more posts tagged with

Accepted answers

Ramzi K

@Jeana

I'm assuming in your example, the top row is the parent of the rest of the rows?

If you want to return the max date in the children range if ANY of the rows are in the next week, then this is your formula:

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

and it should return 11/17/20

If however you want to return the max date only for those rows that are in the next week (have a 1 in the Next Week Column) then this is your formula:

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

If this still doesn't help, let's keep at it! Send me more info and we'll get you going.

Cheers,

Ramzi

All comments

Ramzi K

@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

Jeana

@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

Ramzi K

@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

Jeana

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)

Appreciate your time.

Jeana

Ramzi K

@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

Jeana

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.

Screen Shot 2020-10-13 at 11.19.51 AM.png

Thank you!

Jeana

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)))

Ramzi K

@Jeana

I'm assuming in your example, the top row is the parent of the rest of the rows?

If you want to return the max date in the children range if ANY of the rows are in the next week, then this is your formula:

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

and it should return 11/17/20

If however you want to return the max date only for those rows that are in the next week (have a 1 in the Next Week Column) then this is your formula:

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

If this still doesn't help, let's keep at it! Send me more info and we'll get you going.

Cheers,

Ramzi

Jeana

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.