Alert when tasks are planned to finish after a date in another task (deadline)

Hello all! I am trying to program a sheet to alert users when any finish date in the sheet goes beyond a specific deadline task in the sheet.

I foresee it working like: User changes a start date, and several tasks down the dependency waterfall, a finish date gets pushed beyond the deadline task's date, and an error shows up requiring the dates to be pulled up or the deadline to be moved out for the date to be changed. Has anyone been able to setup a formula that could enable this?

Find more posts tagged with

Sheets

Conditional Formatting

Project Management

Workflows in Smartsheet

Accepted answers

All comments

DKazatsky

Hi @NBookbinder

Here is one way...

Add a column that is a checkbox called "Past Deadline?" or whatever you choose. In that column use a formula that sets the checkbox: =IF([End Date]@row > [Deadline Date]$1, 1) and then create an alerting workflow that is based on the "Past Deadline?" field being checked.

**NOTE: The forlmula uses a cell reference "[Deadline Date]$1" so cannot be turned into a column formula. You will need to drag this formula, or cut-and-paste, into every row.

Hope this helps,

Dave

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.