# Need help calculating Time (New to Smartsheet Formulas)

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!

Tags:

• =((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)

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

• 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

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

• 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?

• @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....🤦‍♀️

• 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

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

• 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)

• 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)

• =((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)

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

• Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!