Calculating time

Lbrady

Hello All,

I'm a new user to Smartsheet and am trying to figure out the formula for calculating time. I have a start time column and an end time column and am trying to figure out the formula to calculate the amount of time in minutes. Any help would be appreciated!

brianschmidt

Smartsheet is not set up for calculation time durations the way it is dates. However, I think I figured out a way to do this even listing times as you have here.

First, you need to create two "helper" text columns that will help isolate the hour markers and convert them from text to recognizable values (can be hidden once set up). Additionally, it will calculate how far in the hour your minutes by adding it as a decimal.

In the "Start Time Hour Helper" column, I have the following column formula:

=IF(LEN([Start Time]@row) = 7, VALUE(LEFT([Start Time]@row, 1)) + (VALUE(MID([Start Time]@row, 3, 2)) / 60), IF(LEN([Start Time]@row) = 8, VALUE(LEFT([Start Time]@row, 2)) + (VALUE(MID([Start Time]@row, 4, 2)) / 60), "ENTER VALID TIME"))

In the "End Time Hour Helper" column, I have the following column formula:

=IF(LEN([End Time]@row) = 7, VALUE(LEFT([End Time]@row, 1)) + (VALUE(MID([End Time]@row, 3, 2)) / 60), IF(LEN([End Time]@row) = 8, VALUE(LEFT([End Time]@row, 2)) + (VALUE(MID([End Time]@row, 4, 2)) / 60), "ENTER VALID TIME"))

Now, in your "Training Duration (min)" column, you need to be able to show the difference in hours multiplied by 60 minutes, while also accounting for the chance that hours may cross over from AM to PM since this example does not use military time. For this column, I wrote the following formula:

=IF(OR(AND(CONTAINS("AM", [Start Time]@row), CONTAINS("AM", [End Time]@row)), AND(CONTAINS("PM", [Start Time]@row), CONTAINS("PM", [End Time]@row))), ([End Time Hour Helper]@row - [Start Time Hour Helper]@row) * 60, IF(VALUE(LEFT([End Time]@row, 2)) = 12, ([End Time Hour Helper]@row - [Start Time Hour Helper]@row) * 60, (([End Time Hour Helper]@row + 12) - [Start Time Hour Helper]@row) * 60))

Assumptions:

-Sessions are in the same day (none that cross the overnight threshold)

-You will write start and end times one of two ways (with either 7 or 8 characters, including spaces). To ensure accuracy, I would add all the hours in the day as drop down values in your Start and End Time columns formatted the same way, assuming sessions start at predictable intervals (i.e. every half hour, 15 minutes, hour, etc.)

Here's a screenshot of what the finished product of these formulas looks like on my screen:

In any case, this was a fun one to figure out. Hope this helps!:)

brianschmidt

Smartsheet is not set up for calculation time durations the way it is dates. However, I think I figured out a way to do this even listing times as you have here.

First, you need to create two "helper" text columns that will help isolate the hour markers and convert them from text to recognizable values (can be hidden once set up). Additionally, it will calculate how far in the hour your minutes by adding it as a decimal.

In the "Start Time Hour Helper" column, I have the following column formula:

=IF(LEN([Start Time]@row) = 7, VALUE(LEFT([Start Time]@row, 1)) + (VALUE(MID([Start Time]@row, 3, 2)) / 60), IF(LEN([Start Time]@row) = 8, VALUE(LEFT([Start Time]@row, 2)) + (VALUE(MID([Start Time]@row, 4, 2)) / 60), "ENTER VALID TIME"))

In the "End Time Hour Helper" column, I have the following column formula:

=IF(LEN([End Time]@row) = 7, VALUE(LEFT([End Time]@row, 1)) + (VALUE(MID([End Time]@row, 3, 2)) / 60), IF(LEN([End Time]@row) = 8, VALUE(LEFT([End Time]@row, 2)) + (VALUE(MID([End Time]@row, 4, 2)) / 60), "ENTER VALID TIME"))

Now, in your "Training Duration (min)" column, you need to be able to show the difference in hours multiplied by 60 minutes, while also accounting for the chance that hours may cross over from AM to PM since this example does not use military time. For this column, I wrote the following formula:

=IF(OR(AND(CONTAINS("AM", [Start Time]@row), CONTAINS("AM", [End Time]@row)), AND(CONTAINS("PM", [Start Time]@row), CONTAINS("PM", [End Time]@row))), ([End Time Hour Helper]@row - [Start Time Hour Helper]@row) * 60, IF(VALUE(LEFT([End Time]@row, 2)) = 12, ([End Time Hour Helper]@row - [Start Time Hour Helper]@row) * 60, (([End Time Hour Helper]@row + 12) - [Start Time Hour Helper]@row) * 60))

Assumptions:

-Sessions are in the same day (none that cross the overnight threshold)

-You will write start and end times one of two ways (with either 7 or 8 characters, including spaces). To ensure accuracy, I would add all the hours in the day as drop down values in your Start and End Time columns formatted the same way, assuming sessions start at predictable intervals (i.e. every half hour, 15 minutes, hour, etc.)

Here's a screenshot of what the finished product of these formulas looks like on my screen:

In any case, this was a fun one to figure out. Hope this helps!:)

Lbrady

Wow, Brian, this is incredibly impressive! I definitely wouldn't have figured this out on my own. Thank you so much for your help!

MSather

Holy Moly, this is awesome!! And exceptionally helpful! I'm brand new to Smart Sheet but I hope one day to be able to configure formulas like this one. Thanks again 🦄

brianschmidt

https://community.smartsheet.com/discussion/comment/436791#Comment_436791

Glad it was helpful! I'm sure you'll be up and running with formulas like this in no time!:)