How to calculate the diffrence between two times?
Hi, want to calculate the difference between when a load was scheduled to depart and when it actually departed. just need the hours to report.
any help and suggestions?
Answers

Hello @Simran Curry  I would recommend creating a helper column in which you would subtract the Scheduled Depart time from the Actual Depart Time. Then in the Hours Late column, I would multiply the result from the helper column by 24. That should do it!
Linda

@Linda Manduchova thankyou for replying, but i am unbale to do that, it does not work as these are text/number columns.

I believe the only way to do it is to take the text string apart, use the pieces to find the difference in minutes between the two dates and times, then divide that difference into hours.
I had a go at doing something similar yesterday for someone else, had a couple of hiccups, and believe I now have a solution. I have adapted that post for you and pasted it below. My column headings are different from yours but you can change yours, paste my formulas as they are, then change your headings back and your formulas will update. That seems safer than me trying to change mine and having the explanations no longer make sense!
What we are doing
 We are taking the date and time stamp text strings.
 We are extracting numbers from those text strings.
 We will use those numbers to do some math to find the time (in hours) between the two time stamps.
 In other words, we take the first two columns shown here and create the third:
Situations covered (aka why this seems so complicated)
 We cannot simply subtract one from the other in smartsheet as there isn't a date time column type.
 We cannot split this into DATEONLY and TIME and subtract these parts separately as the TIME function uses working hours, which may not be 24 hours a day.
 We know that for timezones that are not UTC the Date part of the systemgenerated timestamps behaves weirdly, so (unless you are in the UTC timezone) we can't simply subtract one date from the other to get the number of days between them. If your string is not created by a systemgenerated column we could simplify step 1, but I am going to assume it is.
 We know some timestamps are in 12hour and some in 24hour clock. We will use an IF to apply the appropriate formula. Again, as yours appear to be 12hour clock we could simplify the formula in steps 2 and 3.
Notes
 Created is the systemgenerated Created Date column.
 Modified is the systemgenerated Modified Date columns.
 All other columns are Text/Number.
 If you copy and paste formula be careful that the quotation marks don't convert into smart quotes. If this is an issue, try pasting to a very basic text editor, such as notepad, then into smartsheet.
 All the formulas can be converted to column formulas.
Step 1  Days between dates in minutes
Formula
=((NETDAYS(DATE(VALUE(20 + MID(Created@row, 7, 2)), VALUE(LEFT(Created@row, 2)), VALUE(MID(Created@row, 4, 2))), DATE(VALUE(20 + MID(Modifed@row, 7, 2)), VALUE(LEFT(Modifed@row, 2)), VALUE(MID(Modifed@row, 4, 2))))  1) * 24 * 60)
Explanation
This formula uses the DATE function to create a date from the date in the Created column (we don't use DATEONLY due to issue 3 above). This part:
DATE(VALUE(20 + MID(Created@row, 7, 2)), VALUE(LEFT(Created@row, 2)), VALUE(MID(Created@row, 4, 2)))
And does the same for the Modified column. This part:
DATE(VALUE(20 + MID(Modifed@row, 7, 2)), VALUE(LEFT(Modifed@row, 2)), VALUE(MID(Modifed@row, 4, 2)))
It then works out the NETDAYS between them. 1 is subtracted to account for today.
The result is the number of days between the two dates.
This is then multiplied by 24 to get the number of hours and then by 60 to get the number of minutes. (You could multiply by 1440 but I have left it as two multiplications to make it a little easier to understand and adapt).
Example
Step 2  Time Stamp as Hours Since Start of Day (Created)
Formula
=IF(RIGHT(Created@row, 1) = "M", (IF(VALUE(LEFT(RIGHT(Created@row, LEN(Created@row)  9), FIND(":", RIGHT(Created@row, LEN(Created@row)  9))  1)) = 12, 0, VALUE(LEFT(RIGHT(Created@row, LEN(Created@row)  9), FIND(":", RIGHT(Created@row, LEN(Created@row)  9))  1))) * 60) + IF(RIGHT(Created@row, 2) = "PM", 720, 0), (VALUE(LEFT(RIGHT(Created@row, LEN(Created@row)  9), FIND(":", RIGHT(Created@row, LEN(Created@row)  9))  1)) * 60)) + VALUE(MID(Created@row, FIND(":", Created@row) + 1, 2))
Explanation
The formula starts with an IF function to check whether the Created column ends with "M". If it does, the function to calculate minutes since midnight for 12hour clock times is used. If there is no "M", a simpler formula is used for those with a 24hour clock format.
Note  If there are 12hour clocks in nonEnglish languages (where AM and PM aren't used) a change will be needed here.
More detail
The formula takes the hour part of the timestamp and uses that to find the minutes since midnight. If this is 24 hour clock that is simply hours times 60. This part:
(VALUE(LEFT(RIGHT(Created@row, LEN(Created@row)  9), FIND(":", RIGHT(Created@row, LEN(Created@row)  9))  1)) * 60))
If this is 12 hour clock it converts 12 to 0 (as 12:30 is 0 hours since midnight or noon). It multiples the hours by 60. This part:
(IF(VALUE(LEFT(RIGHT(Created@row, LEN(Created@row)  9), FIND(":", RIGHT(Created@row, LEN(Created@row)  9))  1)) = 12, 0, VALUE(LEFT(RIGHT(Created@row, LEN(Created@row)  9), FIND(":", RIGHT(Created@row, LEN(Created@row)  9))  1))) * 60)
It also adds an extra 720 minutes if the time is PM. This part:
+ IF(RIGHT(Created@row, 2) = "PM", 720, 0)
It then adds the minutes part of the timestamp. This part:
+ VALUE(MID(Created@row, FIND(":", Created@row) + 1, 2))
Example
Step 3  Time Stamp as Hours Since Start of Day (Modified)
Formula
=IF(RIGHT(Modifed@row, 1) = "M", (IF(VALUE(LEFT(RIGHT(Modifed@row, LEN(Modifed@row)  9), FIND(":", RIGHT(Modifed@row, LEN(Modifed@row)  9))  1)) = 12, 0, VALUE(LEFT(RIGHT(Modifed@row, LEN(Modifed@row)  9), FIND(":", RIGHT(Modifed@row, LEN(Modifed@row)  9))  1))) * 60) + IF(RIGHT(Modifed@row, 2) = "PM", 720, 0), (VALUE(LEFT(RIGHT(Modifed@row, LEN(Modifed@row)  9), FIND(":", RIGHT(Modifed@row, LEN(Modifed@row)  9))  1)) * 60)) + VALUE(MID(Modifed@row, FIND(":", Modifed@row) + 1, 2))
Explanation
This is the same formula as step 2 but for the modified column. If your modified column is one column to the right of Created, you can drag the formula from Step 2, one column to the right, and the column names will update automatically.
Example
Step 4  Find the hours between the two dates and times
Formula
=ROUND((((NETDAYS(DATE(VALUE(20 + MID(Created@row, 7, 2)), VALUE(LEFT(Created@row, 2)), VALUE(MID(Created@row, 4, 2))), DATE(VALUE(20 + MID(Modifed@row, 7, 2)), VALUE(LEFT(Modifed@row, 2)), VALUE(MID(Modifed@row, 4, 2))))  1) * 24 * 60) + [Modified Mins since 00]@row  [Created Mins since 00]@row) / 60, 2)
Explanation
We can subtract the minutes into the day that the created timestamp is from the minutes into the day that the modified timestamp is. This is the difference in minutes between the times. It could be positive (if the row was modified at a time after the created time on a subsequent day) or negative (if the row was modified earlier in the day than the created time).
We then add this to the formula from step 1, which gave us the minutes between the two dates.
We now have minutes between created and modified.
We divide that by 60 to get the hours.
And we round to two decimal places for neatness. You can change the 2 to whatever you want  it is the very last number in the formula.
Example
Extra
If you didn't want to include the two gray columns in your sheet you can use the formulas that are within them in the pink column. It will make it harder to read/adapt but will work. The formula for the total duration in hours (step 4) without using the gray columns is:
=ROUND(((((NETDAYS(DATE(VALUE(20 + MID(Created@row, 7, 2)), VALUE(LEFT(Created@row, 2)), VALUE(MID(Created@row, 4, 2))), DATE(VALUE(20 + MID(Modifed@row, 7, 2)), VALUE(LEFT(Modifed@row, 2)), VALUE(MID(Modifed@row, 4, 2))))  1) * 1440) + (IF(RIGHT(Modifed@row, 1) = "M", (IF(VALUE(LEFT(RIGHT(Modifed@row, LEN(Modifed@row)  9), FIND(":", RIGHT(Modifed@row, LEN(Modifed@row)  9))  1)) = 12, 0, VALUE(LEFT(RIGHT(Modifed@row, LEN(Modifed@row)  9), FIND(":", RIGHT(Modifed@row, LEN(Modifed@row)  9))  1))) * 60) + IF(RIGHT(Modifed@row, 2) = "PM", 720, 0), (VALUE(LEFT(RIGHT(Modifed@row, LEN(Modifed@row)  9), FIND(":", RIGHT(Modifed@row, LEN(Modifed@row)  9))  1)) * 60)) + VALUE(MID(Modifed@row, FIND(":", Modifed@row) + 1, 2)))  (IF(RIGHT(Created@row, 1) = "M", (IF(VALUE(LEFT(RIGHT(Created@row, LEN(Created@row)  9), FIND(":", RIGHT(Created@row, LEN(Created@row)  9))  1)) = 12, 0, VALUE(LEFT(RIGHT(Created@row, LEN(Created@row)  9), FIND(":", RIGHT(Created@row, LEN(Created@row)  9))  1))) * 60) + IF(RIGHT(Created@row, 2) = "PM", 720, 0), (VALUE(LEFT(RIGHT(Created@row, LEN(Created@row)  9), FIND(":", RIGHT(Created@row, LEN(Created@row)  9))  1)) * 60)) + VALUE(MID(Created@row, FIND(":", Created@row) + 1, 2)))) / 60), 2)

Were you able to calculate the durations between your timestamps? Let me know if you need any help.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.2K Get Help
 360 Global Discussions
 199 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 444 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!