Most Recent Date, if blank use filled in date

[Rachael_Castela]

Hello!

In my Objectives tab, I want to display the “Upcoming Step Due Date”, which should be calculated by using the most recent date between the Revised Completion date and Projected completion date, found in the Steps Table. If the Revised Completion date is empty (row 4), it should default to the Projected completion date.

I’ll then want to determine how many weeks until that object is due.

Can you help me determine 1) how to get the most recent date and 2) how to create a formula for the weeks until that date?

THANK YOU!

[Jeremy_Oglesby]

Hi @Rachael_Castelaz,

The first step is that you need to consolidate your “Projected” and “Revised” dates into a single field to be Rolled up in the {Upcoming Steps Due Date} field. So in your Steps table, you need a new Formula field that says:

IF(
   {Revised Completion date},
   {Revised Completion date},
   {Projected completion date}
)

I think for the {Upcoming Step Due Date} field, you can set your Rollup field to filter for only a subset of records from the linked record field it points to. I assume you are marking steps as “completed” in some way, so you should be able to filter that rollup to show only “non-completed steps”, whatever that might mean in your setup.

Then, you can use the Rollup formula to show the MIN() date in your new Formula field that was created above, to get the next upcoming date for a “non-completed” step.