Need to calculate a date based on data in another sheet

wkhuser

I'm creating what is basically a notification system that's too complicated for just Automations.

In a one sheet I've set up a Cadence by a Category. So the category might be Webinar. The cadence is captured in multiple columns labeled First Push, Second Push, etc. Each of those houses a number representing the number of days BEFORE an event date.

In my target sheet, I have the actual Events. Event Name = AwesomeWebinar, Category = Webinar. The Event Date = actual date of the event (07/26/23, for instance). I have multiple columns (First Date, Second Date, etc.), each to house a calculated date using the Event Date from within this sheet, and the First Push, Second Push number from the first table.

If the First Push field has the number 45, then AwesomeWebinar's First Date field should show 7/26/23 - 45 days, or 6/11/23. The June date is what I want to see.

(For background, I do have the Formulas reference set and have looked in the Date section. The date calculation info doesn't reference an actual formula name, just does a quick calculation after the "=". The rest of the functions under there (DATE, TODAY, etc.) do not seem to be what I need.)

Paul Newcome

Try this:

=IF(INDEX({First Push # Column}, MATCH(Category@row, {Category Column}, 0)) <> "", [Event Date]@row - INDEX({First Push # Column}, MATCH(Category@row, {Category Column}, 0)))

Paul Newcome

You would need to use an INDEX/MATCH to pull in the number and then have it subtract from the date.

=[Event Date]@row - INDEX({First Push # Column}, MATCH(Category@row, {Category Column}, 0))

wkhuser

chef's kiss! Thank you!

wkhuser

sorry, follow up!

Some categories don't have a "Fifth Push", for example because the marketing lead time is much shorter. I want to have the cell be empty or have something I can key off of in the Condition portion of an Automation. Is the final argument in the INDEX formula capable of doing that?

Right now the 0 as the last thing is setting the cell equal to the actual event date if the Fifth Push column is blank.

Paul Newcome

Try this:

=IF(INDEX({First Push # Column}, MATCH(Category@row, {Category Column}, 0)) <> "", [Event Date]@row - INDEX({First Push # Column}, MATCH(Category@row, {Category Column}, 0)))

wkhuser

That worked! Thanks for the heavy lifting. I can think in terms of SQL but don't know how to translate that into Smartsheet language.

Paul Newcome

Happy to help. 👍️