How to calculate the diffrence between two times?

Simran Curry

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?

Linda Manduchova

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

Simran Curry

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

KPH

Hi @Simran Curry

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)

1. We cannot simply subtract one from the other in smartsheet as there isn't a date time column type.
2. 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.
3. We know that for timezones that are not UTC the Date part of the system-generated 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 system-generated column we could simplify step 1, but I am going to assume it is.
4. We know some timestamps are in 12-hour and some in 24-hour clock. We will use an IF to apply the appropriate formula. Again, as yours appear to be 12-hour clock we could simplify the formula in steps 2 and 3.

Notes

• Created is the system-generated Created Date column. 
• Modified is the system-generated 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 12-hour clock times is used. If there is no "M", a simpler formula is used for those with a 24-hour clock format.

Note - If there are 12-hour clocks in non-English 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)

KPH

Hi @Simran Curry

Were you able to calculate the durations between your timestamps? Let me know if you need any help.