How to enter 'Remaining Duration' (instead of % Complete)

Mike McEvoy

Hi,

When updating tasks, I want to enter 'Remaining Duration' and not a % Complete. Does anyone know if there is a way to do this please? So e.g. if a task is 10 days duration, I don't want to say 50% complete, I want to say there's 5 days remaining.

Thanks, Mike

Genevieve P.

Hi @Mike McEvoy

You can set up a new Text/Number column then use a column formula with the NETWORKDAYS Function and TODAY Function to identify how many working days are left (if the End Date is in the future or today, and if the Start Date is in the past or today).

For example:

=IF(AND([End Date]@row > TODAY(), [Start Date]@row <= TODAY()), NETWORKDAYS(TODAY(), [End Date]@row), "")

Let me know if this is what you were looking to do!

Cheers,

Genevieve

Mike McEvoy

Genevieve, thank you sooo much for taking the time to respond. I'm new to smart sheets. I evaluated it a few years back but at the time it didn't support critical path or baselines so I didn't pursue it. I'm back evaluating it because I see the power of the whole collaboration piece, like Google sheets. I've been using MS Project for 20+ years and have many templates, reports, macros, views, fields etc. built up over that period. It is a bit strange to me, that bedrock items such as 'remaining duration' 'actual duration' 'free slack' 'total slack' etc. aren't default items in smart sheets. Or the ability to push remaining parts of a task to the future.

Updating tasks by entering % complete is poor practice in my view, much better practice is to update 'remaining duration'. Now I need to work out how to enter remaining duration and have the % complete update, I'll give that a go.

Thanks again, Mike

Genevieve P.

Hi @Mike McEvoy

No problem! I completely understand. Smartsheet has had a lot of updates over the last few years but it is a different program than MS Project.

Here's a free webinar that goes through our Project Settings, if that helps:SmartStart: Project Management

And another one that is specific to the Baseline functionality: Keep Your Projects on Track with Baselines

Cheers,

Genevieve

Mike McEvoy

Hi again Genevieve, I've played around some more. The only default scheduling parameters I see are Start Finish Duration % Complete Baseline Start Baseline Finish and Variance. After that you have to code everything, so no Remaining Duration, no Free Slack, no Total Slack amongst a host of others. You've given me code for Remaining Duration but you can't really code that, MS Project allows you to write a value to e.g. Remaining Duration or if you don't, it will. And that's important as best practice is to update a schedule by entering the Actual Start (one time only) and then entering Remaining Duration.

And there doesn't appear to be a method to move incomplete parts forward. So you'd have to manually move any finish date that was late.

And then lastly it doesn't appear you can create views and tables so for every column you need, it's in your Gantt chart or grid view. So you'd be adding and hiding columns the whole time. 

For sure there are some nice features, the online collaboration, the chat, attaching files to tasks and so on. I'd love to be convinced otherwise but from what I can see I couldn't envisage smartsheet as a replacement for MSP.

I've asked several colleagues of mine from different countries. Only one of them was using smartsheet and his organisation uses Agile. I would love to connect with people who have successfully transitioned from MSP to SS.

Mike

Genevieve P.

Hi @Mike McEvoy

Yes, you are correct with most of your comparisons. However, for creating different views, one way to do this would be to use a Project Sheet as the source for a Row Report.

In a Report you can select which columns to show from the underlying sheet (even if those columns are hidden in the sheet). You can filter by rows, too, if needed, then embed Reports on Dashboards to have a higher-level overview. There are lots of ways to display information based on what your end-goal is.

If you'd like to discuss the benefits of using Smartsheet from a Project Management perspective, I would recommend reaching out to Smartsheet Sales, here.

Cheers,

Genevieve

Bill H

https://community.smartsheet.com/discussion/comment/335030#Comment_335030

I tried using this formula, and it just returns, "unparseable". Is there anything special needed to make it work?

Genevieve P.

Hi @Bill H

Is it possible you have different column names, so the formula was unable to find the "Start Date" or "End Date" column?

For example, if your column names are "Start" and "Finish", then the formula would look like this:

=IF(AND(Finish@row > TODAY(), Start@row <= TODAY()), NETWORKDAYS(TODAY(), Finish@row), "")

If this hasn't helped, it would be useful to see a screen capture of the formula open in your sheet with the column names included in the image, but please block out sensitive data.

Cheers,

Genevieve