Need help calculating Time (New to Smartsheet Formulas)

Jani Luna

Hi all - I've searched and read through various questions/ responses regarding Time calculations and have come to the realization that I need help.

I have a Start Time and End Time (Start & End of Trainings) - I used a dropdown from 07:00 to 23:30 (We typically train within business hours 8a-5p EST - but every so often there is one that is is at 7 am or after hours ).

Column Name: Start Date (Column Type: DATE)

Column Name: End Date (Column Type: DATE)

Column Name: Start Time (EST) (Column Type: Single Select Dropdown)

Column Name: End Time (EST) (Column Type: Single Select Dropdown)

I would like to have the following columns calculated:

Duration (Hours) & Duration (Minutes)

I was able to create a NetworkDays formula & Minutes but it's not accurate data as it does not take into account actual hours trained (some training are 3.5 hrs etc..) I would LOVE the duration auto-calculated vs manually adding duration to obtain the data..

Any thoughts on how to go about this - changes I should make to my existing columns?

Thanks!

Mark Cronk

=((VALUE(LEFT([End Time (EST)]@row, FIND(":", [End Time (EST)]@row) - 1)) + (VALUE(RIGHT([End Time (EST)]@row, 2)) / 60)) - (VALUE(LEFT([Start Time (EST)]@row, FIND(":", [Start Time (EST)]@row) - 1)) + (VALUE(RIGHT([Start Time (EST)]@row, 2)) / 60))) * (([End Date]@row - [Start Date]@row) + 1)

Mark Cronk

Hi @Jani Luna ,

@Paul Newcome is the Smartsheet time genius. Borrowing from him:

Rename your [Duration (Hours)] column as [Duration] and enter this formula:

=INT(DurHelp@row) + ":" + IF((DurHelp@row - INT(DurHelp@row)) * 60 < 10, "0") + (DurHelp@row - INT(DurHelp@row)) * 60

Rename your [Duration (Minutes)] column as [DurHelp], for duration helper. Enter this formula:

=((VALUE(LEFT([End Time (est)]@row, FIND(":", [End Time (est)]@row) - 1)) + VALUE(RIGHT([End Time (est)]@row, 2)) / 60) + ([End Date]@row - [Start Date]@row) * 24) - (VALUE(LEFT([Start Time (est)]@row, FIND(":", [Start Time (est)]@row) - 1)) + VALUE(RIGHT([Start Time (est)]@row, 2)) / 60)

If the course goes longer than a day the duration includes the night. You can adjust the duration to remove the night if you want to track class hours.

Work?

Mark

Paul Newcome

Thanks for the shoutout, @Mark Cronk

@Jani Luna My first question would be... Are your trainings actually running overnight?

So we start at 8:00am on the 6th and we have a continuous training until 12:00pm on the 7th for a total duration of 28 hours?

Jani Luna

@Mark Cronk & @Paul Newcome - Hi!

No, they do not run overnight. Each row represents a different training and the Start and End time for each day of the training. Training that starts on 5-Jan to 6-Jan..started at 0900 to 1200 (3hrs) each day (Total Training of 6 hrs.)

I originally had these extra 3 columns:

Duration (Work Days): =NETWORKDAYS([Start Date]@row, [End Date]

Duration (Hours):=[Duration (Work Days)]@row * 24

Duration (Mins): =[Duration (Hrs)]@row * 60

And then realized that would not work....🤦‍♀️

Paul Newcome

Ok. So it is the hourly duration between start and end time multiplied by the number of days?

In that case I would suggest (for a total duration):

=((VALUE(LEFT([End Time (est)@row, FIND(":", [End Time (est)@row) - 1)) + (VALUE(RIGHT([End Time (est)@row, 2)) / 60)) - (VALUE(LEFT([Start Time (est)@row, FIND(":", [Start Time (est)@row) - 1)) + (VALUE(RIGHT([Start Time (est)@row, 2)) / 60))) * ((End Date]@row - [Start Date]@row) + 1)

This converts the End Time into a number:

(VALUE(LEFT([End Time (est)@row, FIND(":", [End Time (est)@row) - 1)) + (VALUE(RIGHT([End Time (est)@row, 2)) / 60))

This converts the Start Time into a number:

(VALUE(LEFT([Start Time (est)@row, FIND(":", [Start Time (est)@row) - 1)) + (VALUE(RIGHT([Start Time (est)@row, 2)) / 60))

This gives us the number of days:

((End Date]@row - [Start Date]@row) + 1)

Then we put everything together like so:

=(End Time Number - Start Time Number) * Number Of Days

Jani Luna

Newbie here..... Do I first adjust my columns as @Mark Cronk suggested? And then include a Total Duration column with the @Paul Newcome formula?

Paul Newcome

For the solution I posted, you would not need to change any existing data. You would just use the formula as is to calculate the total duration.

After reviewing my previously posted formula, I did find where I had missed some closing square brackets around some column names. Here it is corrected:

=((VALUE(LEFT([End Time (est)]@row, FIND(":", [End Time (est)]@row) - 1)) + (VALUE(RIGHT([End Time (est)]@row, 2)) / 60)) - (VALUE(LEFT([Start Time (est)]@row, FIND(":", [Start Time (est)]@row) - 1)) + (VALUE(RIGHT([Start Time (est)]@row, 2)) / 60))) * ((End Date]@row - [Start Date]@row) + 1)

Jani Luna

I also attempted to change the lowercase (est) to (EST) ..to no avail.

=((VALUE(LEFT([End Time (EST)]@row, FIND(":", [End Time (EST)]@row) - 1)) + (VALUE(RIGHT([End Time (EST)]@row, 2)) / 60)) - (VALUE(LEFT([Start Time (EST)]@row, FIND(":", [Start Time (EST)]@row) - 1)) + (VALUE(RIGHT([Start Time (EST)]@row, 2)) / 60))) * ((End Date]@row - [Start Date]@row) + 1)

Mark Cronk

=((VALUE(LEFT([End Time (EST)]@row, FIND(":", [End Time (EST)]@row) - 1)) + (VALUE(RIGHT([End Time (EST)]@row, 2)) / 60)) - (VALUE(LEFT([Start Time (EST)]@row, FIND(":", [Start Time (EST)]@row) - 1)) + (VALUE(RIGHT([Start Time (EST)]@row, 2)) / 60))) * (([End Date]@row - [Start Date]@row) + 1)

Jani Luna

@Paul Newcome & @Mark Cronk - Thank you!!!

Paul Newcome

Happy to help. 👍️