Selecting content from one cell over another

Hi not sure where to start with this one

I have a range of cells where the dates may change. in the summary cell i want the most recent updated date. the cells are set up as following

Tittle Submission Original Submission New Actual Submission Date

Submission date 1/1/21 1/10/21 1/9/21

If no revised "new" dates have been set the cell would be blank along with the actual submission date

So really if date only in submission original, take that date, if date in submission new take that date, if date in actual submission take that date.

Look forward to you insights.

Find more posts tagged with

Accepted answers

Jason Albrecht

Thanks @Mike DeLuca, this helps a lot.

While I'm sure there are others who could offer a cleaner solution to the one below, I'm confident this should work: (where <>"" is another way of saying 'is not blank')

= IF ([Submission Actual]@row <>"", [Submission Actual]@row, IF([Submission Revised]@row <>"", [Submission Revised]@row, [Submission Original]@row))

This is working the IF formula backwards, looking for data in 'Actual' first, then 'Revised' and if both blank, 'Original' data.

Hopefully I've interpreted the request correctly, and hope this helps?

Mike DeLuca

All comments

Jason Albrecht

Hi Mike,

Maybe the MAX Function?

Mike DeLuca

Hi Jason, have used the Max, but on rear occasions the actual maybe less than the revised date.

Jason Albrecht

Hi Mike,

Am I right in understanding that the "actual date" is driving the response required?

In other words, if the "actual date" cell has a date, the formula should choose this date regardless of any of the other dates?

Am I also understanding that if there is no date in the "actual date" cell, to then choose the next most recent date from the other cells?

If so, I wonder if the formula goes something like this...

IF ([actual date cell] = "", MAX( range of related date cells), [actual date cell])

Hope this makes sense?

Looking forward to further clarification otherwise.

Mike DeLuca

HI @Jason Albrecht

A better description would be.

If date in submission original this goes to = submission latest cell

If date in submission original & Submission revised, select submission revised = submission latest cell

If date in submission original & Submission revised & submission actual, select submission revised = submission latest cell.

The issues is sometimes the submission revised date may actually be less that the submission original. this is why the Max formulate is not always going to work for me.

Hopefully this is clearer than my first descritpion.

Regards Mike

Jason Albrecht

Thanks @Mike DeLuca, this helps a lot.

While I'm sure there are others who could offer a cleaner solution to the one below, I'm confident this should work: (where <>"" is another way of saying 'is not blank')

= IF ([Submission Actual]@row <>"", [Submission Actual]@row, IF([Submission Revised]@row <>"", [Submission Revised]@row, [Submission Original]@row))

This is working the IF formula backwards, looking for data in 'Actual' first, then 'Revised' and if both blank, 'Original' data.

Hopefully I've interpreted the request correctly, and hope this helps?

Mike DeLuca

Thanks @Jason Albrecht Seems to work a treat

Mike DeLuca

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.