MTTR (Duration between Created and last Modified.

Dawie

I have a Form I've got agents filling in, once submitted, there's a Created date and time "stamped"

"Date" then row is modified, the "Modified" row updates, once "Job Completed" is "checked" row gets locked and Duration needs to calculate to the difference, which in this case is 3d 6h 10m

HELP?! I've been all over, with no luck of getting the results I'm looking for......

heyjay

Paul N create a discussion for time based calculations in the link below, hope you find your way into creating what you are looking for.

https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time/p1

Dawie

Hey Heyjay, I have been through all the links and all the tips and hints, but still don't seem to get the result I'm looking for.

Paul Newcome

There is a solution for this here:

https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time

Dawie

Hi Paul,

Thanks a mill, I went through this, but probably either missed something or not understanding it correctly, will have a go at it again and let you know, where I went wrong with what, else I'll just reach out for some guidance and pointers, thanks man.

Dawie

I've tried again and have no idea to get the result I'm looking for, tried to use all the mentioned methods, on all the links, but not winning with this one, I'm missing something, but not seeing the obvious, which is probably in front of me.

KPH

Hi @Dawie

I am not a time expert (we don't move quickly enough to worry about hours!) but I do love a brain teaser. So, I've had a tinker and have a formula that I believe will do what you want. However, this might not be the most efficient way of doing it. But, at the risk of @Paul Newcome calling out my mistakes and oversights (be gentle, Paul!), here goes....

As a single formula, the duration formula is pretty big, so I would suggest not using it as a single formula but adding some helper columns and referencing those to build the formula.

This requires 2 helper columns (created in steps 2 and 3 below), and includes a workaround for those not in the UTC timezone:

=NETDAYS(DATE(VALUE(20 + MID(Date@row, 7, 2)), VALUE(LEFT(Date@row, 2)), VALUE(MID(Date@row, 4, 2))), DATE(VALUE(20 + MID(Modifed@row, 7, 2)), VALUE(LEFT(Modifed@row, 2)), VALUE(MID(Modifed@row, 4, 2)))) - 1 + "d " + ROUNDDOWN(([Modified Date helper]@row - [Created Date helper]@row) / 60, 0) + "h " + ((([Modified Date helper]@row - [Created Date helper]@row) / 60) - ROUNDDOWN(([Modified Date helper]@row - [Created Date helper]@row) / 60, 0)) * 60 + "m "

It is a bit easier to read like this:

I will walk through each part below. The red part (days) is created in step 1, the yellow part (hours) in step 4 (using helper columns created in steps 2 and 3), and the blue part (minutes) in step 5 (also using helper columns created in steps 2 and 3).

I have used your column headings Date for Created Date and Modified for Modified Date.

1. Find the number of days between created and modified dates

If you are in the UTC timezone finding the days between two dates is simply a case of using the NETDAYS function:

=NETDAYS(Date@row, Modifed@row) - 1

Lucky you!

1.1 Workaround for non-UTC time zone

If you are not in a UTC timezone, this is problematic. The system dates look as though they are in the local time. But they are stored in UTC and do weird things, and do so invisibly.

For example, in the PST timezone which is UTC -8, the netdays between two dates changes at 4pm, not midnight, even though the dates and times are displayed in PST. So the netdays calculations are often wrong. In this example, I modified all my rows at 6:49pm. For rows created after 4pm the net days calculation is fine, but rows created before 4pm are treated as if they were created on the previous day.

If you are not in the UTC timezone, we can take the date part from the column, convert it to text, and then back into a date. Then we do the NETDAYS on that. I won't explain this formula fully as you might not need it. But here it is:

=NETDAYS(DATE(VALUE(20 + MID(Date@row, 7, 2)), VALUE(LEFT(Date@row, 2)), VALUE(MID(Date@row, 4, 2))), DATE(VALUE(20 + MID(Modifed@row, 7, 2)), VALUE(LEFT(Modifed@row, 2)), VALUE(MID(Modifed@row, 4, 2)))) - 1

2. Express the Created Date Time as Minutes since the start of the day

We can't do math on a time stamp, but we can do math if we can convert the time into hours and minutes. This formula will do that for us. In steps 2.1-2.5, I explain each part of this formula by creating a separate column for each before combining them. You can skip to step 3 if this makes sense on its own.

=(IF(VALUE(LEFT(RIGHT(Date@row, LEN(Date@row) - 9), FIND(":", RIGHT(Date@row, LEN(Date@row) - 9)) - 1)) = 12, 0, VALUE(LEFT(RIGHT(Date@row, LEN(Date@row) - 9), FIND(":", RIGHT(Date@row, LEN(Date@row) - 9)) - 1))) * 60) + VALUE(MID(Date@row, FIND(":", Date@row) + 1, 2)) + IF(RIGHT(Date@row, 2) = "PM", 720, 0)

2.1 Convert the created time hours into into hours since 12

We start by finding hours since 12 by extracting the number from the string using this formula:

=VALUE(LEFT(RIGHT(Date@row, LEN(Date@row) - 9), FIND(":", RIGHT(Date@row, LEN(Date@row) - 9)) - 1))

We will worry about whether 12 is noon or midnight later.

2.2 Account for 12 really being 0 (mathematically)

While 1:05 am is 1 hour from midnight, 12:05 am is actually 0 hours since midnight, and 12:05 pm is 0 hours since noon, so we can wrap the above formula in an IF to say if the result is 12, then use 0 instead.

=IF([Created time hours since 12]@row = 12, 0, [Created time hours since 12]@row)

Now we have this.

2.3 Convert the created time minutes into minutes since 00

As we did with the hours, we can extract the minutes from the timestamp. This formula extracts the 2 characters after the colon in the system data time column.

=VALUE(MID(Date@row, FIND(":", Date@row) + 1, 2))

2.4 Add 12 hours if the time is in the afternoon

We can use an IF function to add an extra 720 minutes (12 hours) if the timestamp is PM, using this formula.

=IF(RIGHT(Date@row, 2) = "PM", 720, 0)

2.5 Express the time as minutes since the start of the day

We can combine these formulas to express the start time as minutes since the start of the day. We take the revised hours (step 2.2) and multiply that by 60 to get minutes, then add the minutes (step 2,3), then add the extra minutes for times in the afternoon (step 2.4).

I suggest making this a helper column and retaining it to use in the formula later. You do not need to retain the other columns. Those formulas are all in this one.

=(IF(VALUE(LEFT(RIGHT(Date@row, LEN(Date@row) - 9), FIND(":", RIGHT(Date@row, LEN(Date@row) - 9)) - 1)) = 12, 0, VALUE(LEFT(RIGHT(Date@row, LEN(Date@row) - 9), FIND(":", RIGHT(Date@row, LEN(Date@row) - 9)) - 1))) * 60) + VALUE(MID(Date@row, FIND(":", Date@row) + 1, 2)) + IF(RIGHT(Date@row, 2) = "PM", 720, 0)

Here is a summary of what we did.

3. Express the Modified Date as Minutes since the start of the day

If your modified date column is right next to your created date column, you can simply drag the formula in 2.5 to the right and it will update the column name. The formula is exactly the same it just works on the Modified column.

=(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) + VALUE(MID(Modifed@row, FIND(":", Modifed@row) + 1, 2)) + IF(RIGHT(Modifed@row, 2) = "PM", 720, 0)

4. Calculate the difference between Modified and Created Times

Now we have the numbers from steps 2 and 3 we can do some math.

We find the hours using this formula:

=ROUNDDOWN(([Modified Date helper]@row - [Created Date helper]@row) / 60, 0)

Again, I will walk through the steps to create this. You can skip to step 5, if this formula makes sense without explanation.

4.1 Hours between modified and created times

We can start by finding the hours between the two times (which are both expressed as minutes since midnight in the helper columns).

This is a simple subtraction, divided by 60.

=([Modified Date helper]@row - [Created Date helper]@row) / 60

4.2 Whole Hours

If we round this down to 0 decimal places, we have the whole hours. This is the number we will use later in the duration string.

=ROUNDDOWN(([Modified Date helper]@row - [Created Date helper]@row) / 60, 0)

5. Minutes between Modified and Created Times

We find the minutes using this formula:

=((([Modified Date helper]@row - [Created Date helper]@row) / 60) - ROUNDDOWN(([Modified Date helper]@row - [Created Date helper]@row) / 60, 0)) * 60

Again, I will walk through the steps to create this. You can skip to step 6, if this formula make sense without explanation.

5.1 Minutes

If we subtract the difference in hours (3.45 on row 1) and the whole hours (3 on row 1) we have the minutes as a part of an hour (0.45 hours in row 1, which if you multiply by 60 is 27 minutes).

So we take the formula from 4.1, subtract the formula from 4.2, and multiply the result by 60 to get the minutes that we will use in our duration.

=((([Modified Date helper]@row - [Created Date helper]@row) / 60) - ROUNDDOWN(([Modified Date helper]@row - [Created Date helper]@row) / 60, 0)) * 60

Steps 1 - 5 Summary

(Note, I took a break to cook dinner before adding the final columns so the modified dates in the screenshots have changed).

Now we have three columns we can use to create our duration (in orange) and two helper columns (in gray) that are used to create the values in orange.

• Days from Step 1
• Created Date helper from Step 2
• Modified Date helper from Step 3
• Whole Hours from Step 4
• Minutes from Step 5

6. Combine Days, Hours, and Minutes to create a duration

And now for the fun part. You can combine the columns we made in steps 1, 4, and 5 to create the string you need.

=Days@row + "d " + [Whole Hours]@row + "h " + [Minutes ]@row + "m"

If you don't want to keep the orange columns, you could use the formulas within them directly in the duration column like this:

=NETDAYS(DATE(VALUE(20 + MID(Date@row, 7, 2)), VALUE(LEFT(Date@row, 2)), VALUE(MID(Date@row, 4, 2))), DATE(VALUE(20 + MID(Modifed@row, 7, 2)), VALUE(LEFT(Modifed@row, 2)), VALUE(MID(Modifed@row, 4, 2)))) - 1 + "d " + ROUNDDOWN(([Modified Date helper]@row - [Created Date helper]@row) / 60, 0) + "h " + ((([Modified Date helper]@row - [Created Date helper]@row) / 60) - ROUNDDOWN(([Modified Date helper]@row - [Created Date helper]@row) / 60, 0)) * 60 + "m "

Or if you are lucky enough to be in the UTC timezone, like this:

=NETDAYS(Date@row, Modifed@row) - 1+ "d " + ROUNDDOWN(([Modified Date helper]@row - [Created Date helper]@row) / 60, 0) + "h " + ((([Modified Date helper]@row - [Created Date helper]@row) / 60) - ROUNDDOWN(([Modified Date helper]@row - [Created Date helper]@row) / 60, 0)) * 60 + "m "

Here's hoping that's OK. It looks like it will work, to me, at least!

Dawie

Hi @KPH

A bit confusing...

As I am getting lost between the 12h and 24h parts, what is the chance you can provide just the break down in 24h

As the time format I'm using is 20:00 and not 8 PM.

KPH

Step 2 is about finding how many minutes since the start of the day the Created time is. This gives us a value we can compare to the same for Modified time and then subtract one from the other to find the difference. As there isn't a smartsheet function to do this we need to make up our own by looking at what we have and extracting numbers from a string.

I am glad I wrote out each step rather than just the formula as it sounds like the system column formats differ between regions, so not everyone will need all steps.

As my system times are in 12 hr format I can't just use the hours in the timestamp as the number of hours since midnight. In my case, 11 could mean 11 hours since midnight or 11 hours since noon (and therefore 23 hours since midnight). So in step 2.4 I look at the AM and PM part of the timestamp, then add 12 times 60 minutes for any PM times. If you do not have AM and PM, you can skip this step.

You can also skip step 2.2 as thirty minutes past midnight should show as 00:30 for you, whereas for those with AM and PM it shows as 12:30 (so we use an IF to replace 12 with 0, as 12:30 AM is not 12 hours since midnight).

You are aiming for a column with a number in it that is the number of minutes since midnight. If you start with something different to me, how you get there may not be the same way I did.

This might be the answer for your region:

hours (formula from 2.1) multiplied by 60, plus minutes (formula from 2.3)

=(VALUE(LEFT(RIGHT(Date@row, LEN(Date@row) - 9), FIND(":", RIGHT(Date@row, LEN(Date@row) - 9)) - 1))*60)+VALUE(MID(Date@row, FIND(":", Date@row) + 1, 2))

24-hour clock (and UTC time zones) are a lot easier to work with.

Dawie

@KPH

I am even more confused now :D

The formula at the end of your last post, where does that go, do I still need the "Helper Columns" ?

I've shared the above to someone also looking to use it the same as I do and they confirmed the 12h parts are working brilliantly for them though, so we have success for that and the persons is extremely thankful.

But I am still struggling to grasp how to get this to work for my scenario on 24h....

KPH

What we are doing is creating a duration string which is 3 numbers with d, h, and after each. The three numbers being the number of days, hours, and minutes, between the Created and Modified dates/times.

In my example, I put each component into an orange column. I suggest you do the same at least to get started.

The first column is days (step 1) and I think you have that working.

The other columns are minutes and hours. To find the number of minutes and hours between the two timestamps we first need to convert the time into minutes since midnight. We can then subtract one from the other. The Smartsheet TIME function is based on working hours and does not allow us to easily subtract a time from another, so we are not using that.

13:24 minus 11:14 just doesn't work.

So we express 13:24 as 13 hours and 24 minutes since midnight, which is 13x60 plus 24 = 804 minutes.

And then 11:14 is 11 hours and 14 minutes since midnight, which is 11x60 plus 14 = 674 minutes.

Now we can do 804-674 which is 130. So 13:24 is 130 minutes after 11:14.

Steps 2 and 3 are formulas that take the timestamp and do the conversation to minutes since midnight. These create the gray helper columns. Then in steps 4 and 5, we can subtract one from the other to find the difference (in hours and minutes), and create the final two orange columns.

The formula I adapted this morning is to replace the one in step 2 to create the Created Date helper. It is a simplified version of the 12-hour one.

You can use the same simplified version in step 3 as your Modified Date helper. Just replace the column names.

Steps 4 and 5 are exactly the same.

Saying all that, I've actually realized there is a scenario where this does not work (if the modified time is earlier in the day than the created time, but on a different day). So, I will need to revise this slightly. When I write it out I could start from scratch and include the 12hr and 24 hr options to make it clearer. However, although the change to the formula is not too much, taking the screenshots and typing out the instructions takes a long time and I don't have time right now. Can I get back to you?

Dawie

Understood and appreciate your assistance with this, I'll have a crack at it again, I have notice two of the formulas I just copied and pasted, but didn't work, after quite a long rabbit hole I went down, I've picked up copy and paste breaks it, after retyping the entire formula manually it actually worked.

Just need to now ensure I'm using the correct Column type, the correct order and formulas, sssssoooo close to getting this sorted.

Will wait for your feedback.

KPH

UPDATED INSTRUCTIONS

I am starting over with a solution that uses IF functions to determine whether the timestamp is in 12-hour or 24-clock format so that both you and the person you are helping can use the same formula. I haven't included as much explanation in this as I think that might not be helpful.

What we are doing

• We are taking the date and time stamp text strings (the system columns - created date and modified date).
• We are extracting numbers from those text strings.
• We will use those numbers to do some math to find the time (in days, hours, and minutes) between the two time stamps.
• We will then put that back into a text string.
• In other words, we take the first two columns 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.
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.

Notes

• Created is the system-generated Created Date column. (Dawnie, I did not use your name this time as "Date" might get confusing. If you change your column heading you can use these formula as they are, then change your heading, and the formula will update to your new name.)
• 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 number of minutes between the two dates and times

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) +
[Modified Mins since 00]@row - [Created Mins since 00]@row

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.

Example

(I have reused the column from step 1, renamed it, and moved it to the right. Smartsheet also saved at this point so my modified times have all changed from the previous screenshots.)

Step 5 - Create Duration String using Difference in Minutes between Created and Modified

Formula

=ROUNDDOWN([Total Duration in Mins]@row / (24 * 60)) + "d " + 
ROUNDDOWN((MOD([Total Duration in Mins]@row, (24 * 60)) / 60), 0) + "h " + 
MOD([Total Duration in Mins]@row, 60) + "m"

Explanation

This formula starts by calculating the Days by taking the Total Duration in Minutes and dividing it by 24hrs * 60minutes. It then rounds it down to whole days. This part:

ROUNDDOWN([Total Duration in Mins]@row / (24 * 60))

The + "d " + adds the little "d" and the space that then appears in the string and gets ready for the next part.

Then, it takes the MOD of the same calculation (that is the bits after the decimal, the parts of days that are excluded in the formula above when it is Rounded). It then divides that by 60 and rounds down to a whole number. This gives us the hours. This part:

ROUNDDOWN((MOD([Total Duration in Mins]@row, (24 * 60)) / 60), 0)

The + "h " + adds the "h" and the space.

The final part takes the part after the decimal when the Total Duration in minutes is divided by 60. Those are the minutes.

MOD([Total Duration in Mins]@row, 60)

The + "m" adds the "m"

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 minutes without using the gray columns is:

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

You can also paste this into the Duration column 3 times if so inclined, but that will be hard to adjust.

Let me know how you get on.

Paul Newcome

You can use the formulas from the Date/Time/Year link on page 1 of the thread I shared. If you do not need years and months you can leave those portions out. Just follow the formulas and helper columns.

Since you are using the timestamp columns, I would suggest additional helper columns to break out the dates and times from each other into their own columns so you can follow along.

Since you are using 24 hour time, we can simplify the solution a bit in a few different places. I have added another section to the published sheet that outlines the formulas used. There are 4 columns we can skip over altogether, and there are two columns that use different formulas.

https://app.smartsheet.com/b/publish?EQBCT=8b6ddddb66bb4b3db6fbbec745b46795&_gl=1*48v8tv*_ga*MTYwMTgwMzM1Mi4xNjg0NDM4Mzg5*_ga_ZYH7XNXMZK*MTcwODM0OTcyMi41OTIuMS4xNzA4MzUxNTAwLjU1LjAuMA..&_ga=2.70427856.154169351.1708349723-1601803352.1684438389