Formulas for the timing of an event Runsheet

Options

Hi,

I am trying to develop an Event Runsheet that calculates from two columns: 'Start time' and 'Duration' to give me an 'End time'. I would also like all the timings for the event to re-calculate if a 'Duration' entry is changed.

I don't know if this is possible in Smartsheet, I have managed this in Excel but it would be great if it does work in Smartsheet.

Kind Regards (All the way from New Zealand)

Peter ☺️

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    Hi @peter65516 

    Hope you are fine, yes you do that but you need to play around because Unfortunately, as far as I know, it's not possible at the moment to do it by using the same time functions that you use in excel, Can you share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help

    my best wishes for you and for everyone in New Zealand 😍

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 02/24/21
    Options

    @peter65516

    i checked the sheet you shared, could you please send to my Email the excel sheet that you solved your issue on it so i can understand how you do it in excel because the smartsheet you shared with me is not showing what you are trying to do.

    bassam.k@mobilproject.it

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @peter65516

    Yes, this is possible! To do so, you'll just need to ensure that your sheet has Project Settings enabled on it, and that your Start and End date columns are mapped in these settings, see this Help Article: Modify Project Settings.

    Then you will want to enable Dependencies in your Project Settings. Once you've done this, entering any two values for the Start Date, End Date, and Duration columns will automatically calculate the third value. For example, if you add in the Start Date and the Duration, it will automatically calculate the End Date... or if you add in the Start Date and End Date it will automatically adjust the Duration. See: Enable Dependencies and Use Predecessors

    This way, if you have tasks that use Predecessors (so are based on a previous row), and you adjust that first row to have a different Duration, it will automatically adjust the End Date for that row and update any following tasks that are Dependent on it.

    Here's a Project Management Tutorial you may want to review as well.

    Cheers!

    Genevieve

  • peter65516
    peter65516 ✭✭✭✭
    Options

    Thank you, I have looked at enabling project settings and learnt some new things. But this appears to relate to date columns only. The Run sheet I am working needs to calculate minutes e.g. Start time 4.30pm - Duration 7m = Start time in the next row down.

    Any help is gratefully received

    Peter

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @peter65516

    My apologies, I misunderstood what you were looking to do. In this instance @Bassam.M Khalil is correct, there currently isn't a direct way to calculate time within Smartsheet. You can use Duration within Project Settings to identify time, but it will adjust the Start and End columns by the Date (please provide your feedback to the product team through this form, here!)

    @Paul Newcome started a great thread about different Time calculations using formulas, if any of these will help: Formulas for Calculating Time

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!