Formula to show Next Scheduled Date

[Wayne]

Hi Team

I have exhausted google and the community page and can’t seem to find something I was expecting to be quite common.

I have a “Project Overview” and a “Project Details” board. Overview shows the project workflow at a glance with key milestones while to details show every step with scheduled dates for each step, along with the selected milestones mirrored to the overview board.

I’m looking to add a date column to the overview board of the next scheduled day in the workflow so again, at a glance I can see “Project ABC has Stage 5 completed so therefor Stage 6 is scheduled for DATE”

I thought adding a formula Column to the details board and mirroring it across , surely there is a formula to show closest future date to todays date

Interested to know peoples thoughts or work arounds

Thanks!

[JCorrell (Jim - The Monday Man)]

@Wayne,

The devil, as they say, is in the details. Can you provide additional information on the current structure of you boards?

[Wayne]

@JCorrell

Thanks Jim, I’ll attempt to explain a little further with some screen captures below.

Below is the “Overview board”. A place where I can see at a glance where is project is up to.

Below is my Details Board" with a lot more info, status columns and date columns. This feeds the overview board the key milestones. Project workflow progresses left to right.

What I’m looking to achieve is have a date column on the overview board that is the next date a project has something scheduled in.

For example, project at Lot 205, 41 Madeleine Drive has “Finish Off” scheduled for June 30, while the Date column for Lot 209 would display a missed dead line of June 9.

I hope that makes sense

[JCorrell (Jim - The Monday Man)]

@Wayne,

Here is one solution:

Create 2 formula columns on the details board and mirror the second on the overview board.

Formula 1 (add as many statuses and dates as needed):

MIN(IF(AND({Status1}<>"Done", {Date1}<>""), DAYS({Date1}, TODAY()), 9999), IF(AND({Status2}<>"Done", {Date2}<>""), DAYS({Date2}, TODAY()),9999))

Formula 2:

IF(AND({Formula1}<>"",{Formula1}<>9999), FORMAT_DATE(ADD_DAYS(TODAY(), SUM({Formula1},0.999)), "MMM D, YY"),"")

If you need the Next Date to be an actual date column, I would recommend using an Integromat scenario called with a webhook when any column changes on the details board.