nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Smartsheet Community

Connect with peers, share your expertise, and inspire what’s next in Smartsheet — from proven practices to practical how-to insights from fellow users and product experts.

Welcome!

Expected Progress

PamelaH

Hi, which formula can I use to calculate the expected Progress (depending on the current date)?

Thanks

Find more posts tagged with

Formulas

Reports

Comments

Paul Newcome

Could you give more details and share some screenshots?

PamelaH

Hi Paul,

I have the Start Date, End Date, Duration (days) and the Progress given by each responsible. From this Information I would like to calculate the expected Progress, how shall the Project must be going on an specific date.

For example:

I have a Task with 10 days as Duration. by the second day the Progress completed is 20% (that means is running accordingly to plan) and by the third day, the Progress is the same. That means that by the third day the Project has already a delay.

So I would like to know which formula can I use to find out this expected or planned Progress and then I can be able to make a comparison against Progress completed and create alerts if neccesary.

Thanks.

Paul Newcome

If you create a [Projected % Complete] column, you can enter this formula into it...

=(TODAY() - [Start Date]@row) / ([Scheduled End Date]@row- [Start Date]@row)

This will give you how far into the scheduled time you are for whatever today's date is. You can then build your alerts off of whether your actual % complete is less than, greater than, or equal to where you should be (projected % complete).

To show the projected finish date based off of the current progress and the start date, create a date type column [Projected End Date] and insert this formula...

=[Start Date]@row+ ((((TODAY() - [Start Date]@row) / ([Scheduled End Date]@row - [Start Date]@row)) - [Actual % Complete]@row) * ([Scheduled End Date]@row - [Start Date]@row)) + ([Scheduled End Date]@row - [Start Date]@row)

PamelaH

Thank you!

Aubree Clark

I have a similar request: I would like to see how the projected end date of a task will impact the overall projected end date of the project taking the predecessor relationships into account. Any ideas on how to do that with a function or in a dashboard?

Sign in to join the conversation!

Quick Links

Trending discussions

Find MAX non-blank cell with matching criteria
Hi all! I'm working on a column formula that will check whether the Column2ref cell in the current row is blank. If it is blank, the formula finds the maximum value in the ID column where the Trainer Name matches the Trainer Name cell value in the current row and the ID cell value is less than the ID cell value in the…
How to restrict sub-action due dates in a Smartsheet Form?
Hi Smartsheet Community, I'm working on an action management system where users create sub-actions through a Smartsheet Form. The requirement is simple: a sub-action's due date must be earlier than or equal to its parent action's due date. For example, if the parent action is due on June 30, 2027, users should not be able…
Form Submitter
How do I see who submitted a form for a specific sheet?