Sort by two dates, then rank based on ascending sort

Hello all,

I have a sheet with two key column dates. I want to sort by Date1, then sort by Date2 and if a row has both dates, I want to assign it a number (rank it) based on the order it appears in the sort.

I've tried:

=IF([Date 1]@row <> "", IF([Date 2] <> "", RANKEQ([Date 2], [Date 1]:[Date 1])))

but it seems that the RANKEQ formula doesn't like date data types. Any ideas?

I'm also missing the sorting step because I'm not sure how to sort without changing the whole table

Find more posts tagged with

Formulas

Accepted answers

All comments

Seth Morth

Have you tried using a Report or a Report on a Dashboard to do this? They are much more 'automated' of a view of data, whereas the Smartsheet itself will wait for you to manually instruct it to sort.

https://help.smartsheet.com/articles/2482077-report-on-data-from-multiple-sheets

What I am talking about is alluded to and buried within these few words "Reports are bi-directional, so information updated on a report will automatically update back to the underlying sheets and vice versa."

The rank # will not be based on the order it appears in the sort, that ranking will need to be based on the criteria of the sort itself. I am not clear on how to rank against dates, but you can certainly rank based by the text version of the date with a formula where you copy the date of 2/5/2022 and turn it into 20220205 in an adjacent cel and then rank that result. It is a two-step but may be a better approach for you.

Seth

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.