Formulas for the timing of an event Runsheet

peter65516

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 ☺️

Bassam Khalil

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 😍

Bassam Khalil

@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

Genevieve P.

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

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.

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