Subtract 1 time column from another at the row level using the time function return in same format

allison.worley

I am subtracting one time column from another. I was able to get an answer, that appears mathematically correct. However I would like the answer to return in the hour : minute : second format as it started in. Is this possible?

I am subtracting Start time from End time to get Run Time and used the formula below:

=(TIME([End Time]@row) - TIME([Start Time]@row)) * 24 * 60 * 60

below is the result:

Ignore the total cycle time that is another issue.

They are currently calculating each manually.

allison.worley

Here is my work as I found the solution and it may help others. To have the time in a time format I had to solve for the time in a decimal first shown below.

This gave me the total time in minutes. I then created an additional column to create the fraction into a time presentation.

This second column then changed my decimal from the "run time formula test" column to a time amount.

If anyone knows how I could string these together where I wouldn't have to have 2 columns the help would be greatly appreciated!

allison.worley

KPH

You can copy the formula that you have in the Run Time formula Test column (adding a parenthesis at the start and end) and paste it over [Run Time formula test]@row in the second formula. I would copy this into notepad and use find/replace to make sure you get all 5 references to that cell and no extra parentheses.

allison.worley

I just realized this will not quite work for me. I have a few runs that ran over 24 hours. I need to figure out how to take the current formula and add hours : Mins : sec vs my current Mins : Seconds. I know it will just be an addition to the current string but since I figured that one out using an example I'm not exactly sure how to string it together. Any help is welcome. Then I can go back to trying to remove the helper column and only have 1 formula.

KPH

You have start time and end time as time stamps and are calculating the time between the times, correct? If a row runs more than 24 hours, how will this be shown? Do you also have start date and end date?

allison.worley

The time will be cumulative hours : mins : sec. Mins and secs can only have max 60 but hours can run past 24. Since it is time in hours vs time of day. I do not have a date column for this but it will not go past 24 hours. The formula is stuck in the minutes and for those that do move into the 60+ min mark the formula should show hours or 00: if no hours.

KPH

I am concerned that the original formula isn't going to work for you without dates unless all projects end before midnight. Although if the span is always less than 24 hours it won't be too difficult to fix so we'll ignore that for now and focus on the question at hand.

I think you are looking to change a number that is in minutes and parts of minutes into one in hours, minutes, and seconds. You have been able to change it to minutes and seconds but aren't sure how. I will explain how that works first and then (next comment) how you can adapt it.

1. Minutes to minutes and seconds

This is the formula you are using:

=INT([Run Time Formula Test]@row) + ":" + IF(([Run Time Formula Test]@row - INT([Run Time Formula Test]@row)) * 60 < 10, "0") + ([Run Time Formula Test]@row - INT([Run Time Formula Test]@row)) * 60

This is the result (I added some extra example rows at the end):

It is the output of three formulas and a colon joined together into one string (the + shows the join).

Breaking it down, the part in bold here:

=INT([Run Time Formula Test]@row) + ":" + IF(([Run Time Formula Test]@row - INT([Run Time Formula Test]@row)) * 60 < 10, "0") + ([Run Time Formula Test]@row - INT([Run Time Formula Test]@row)) * 60

Gives you the whole number part of Run Time Formula Test. Everything before the decimals. This is 29 in your first row. These are your minutes.

+ ":"

Adds a colon

This part (which is used twice):

=INT([Run Time Formula Test]@row) + ":" + IF(([Run Time Formula Test]@row - INT([Run Time Formula Test]@row)) * 60 < 10, "0") + ([Run Time Formula Test]@row - INT([Run Time Formula Test]@row)) * 60

Takes the number in Run Time Formula Test and subtracts the part before the decimal. This is 0.81677 in your first row. This is a decimal representation of your seconds.

* 60

Converts this from parts of a minute to seconds. If your time in minutes was 29.5 then 0.5 * 60 would be 30, ie 30 seconds.

That formula is used twice. In the first use it is part of an IF

=INT([Run Time Formula Test]@row) + ":" + IF(([Run Time Formula Test]@row - INT([Run Time Formula Test]@row)) * 60 < 10, "0") + ([Run Time Formula Test]@row - INT([Run Time Formula Test]@row)) * 60

This says if the number of seconds is less than 10, return 0. If is it more than 10 return nothing. This is the first digital to appear after your colon. The next digit is produced using the same formula. The IF ensures that if the seconds are 5 the result is :05 and not :5.

=INT([Run Time Formula Test]@row) + ":" + IF(([Run Time Formula Test]@row - INT([Run Time Formula Test]@row)) * 60 < 10, "0") + ([Run Time Formula Test]@row - INT([Run Time Formula Test]@row)) * 60

You can play with each of the formulas to see what that do:

=INT([Run Time Formula Test]@row) + ":"

= ([Run Time Formula Test]@row - INT([Run Time Formula Test]@row)) * 60

= IF(([Run Time Formula Test]@row - INT([Run Time Formula Test]@row)) * 60 < 10, "0")

I'll start another comment with part 2 as this is quite long.

KPH

2. Minutes to hours, minutes and seconds

So now we need to adapt the formula you already had (explained in part 1) so that we don't have a situation like the last row, where we have 73 minutes. Instead we want 1 hour and 13 minutes. The formula is:

=IF(INT([Run Time Formula Test]@row / 60) < 10, "0") + IF(([Run Time Formula Test]@row / 60) >= 1, INT(([Run Time Formula Test]@row / 60)), "0") + ":" + IF(INT((([Run Time Formula Test]@row / 60) - INT([Run Time Formula Test]@row / 60)) * 60) < 10, "0") + INT((([Run Time Formula Test]@row / 60) - INT([Run Time Formula Test]@row / 60)) * 60) + ":" + IF(([Run Time Formula Test]@row - INT([Run Time Formula Test]@row)) * 60 < 10, "0") + ([Run Time Formula Test]@row - INT([Run Time Formula Test]@row)) * 60

I will explain how to build this.

Lets start with minutes that are less than 10 (such as the last row in my example). We need to add a leading 0 to those, just like we did for the seconds.

So we add this at the start of the formula:

=IF(INT([Run Time Formula Test]@row) < 10, "0") + INT([Run Time Formula Test]@row) + ":" + IF(([Run Time Formula Test]@row - INT([Run Time Formula Test]@row)) * 60 < 10, "0") + ([Run Time Formula Test]@row - INT([Run Time Formula Test]@row)) * 60

This changes the last row:

Next a nice easy part - adding the colon to the start:

=":" + IF(INT([Run Time Formula Test]@row) < 10, "0") + INT([Run Time Formula Test]@row) + ":" + IF(([Run Time Formula Test]@row - INT([Run Time Formula Test]@row)) * 60 < 10, "0") + ([Run Time Formula Test]@row - INT([Run Time Formula Test]@row)) * 60

Now we can add the hours.

=IF(([Run Time Formula Test]@row / 60) >= 1, INT(([Run Time Formula Test]@row / 60)), "0")

This takes the Run Time Formula Test and divides it by 60. If the result is equal to or greater than 1, then we have some hours to worry about, and we calculate those as the INT of the Run Time Formula Test divided by 60 (i.e., the whole numbers of the minutes divided by 60). If the result is less than 60 we return 0. There are 0 hours.

We can put that at the start of our formula and now we have:

=IF(([Run Time Formula Test]@row / 60) >= 1, INT(([Run Time Formula Test]@row / 60)), "0") + ":" + IF(INT([Run Time Formula Test]@row) < 10, "0") + INT([Run Time Formula Test]@row) + ":" + IF(([Run Time Formula Test]@row - INT([Run Time Formula Test]@row)) * 60 < 10, "0") + ([Run Time Formula Test]@row - INT([Run Time Formula Test]@row)) * 60

Giving us this:

Don't worry about the original minutes being incorrect, we'll get to them in a bit.

I also added some more rows to illustrate other examples.

Next up is to add a leading 0, just as we've done twice before by checking if the result is less than 10 and returning 0 if it is.

=IF(INT([Run Time Formula Test]@row / 60) < 10, "0") + IF(([Run Time Formula Test]@row / 60) >= 1, INT(([Run Time Formula Test]@row / 60)), "0") + ":" + IF(INT([Run Time Formula Test]@row) < 10, "0") + INT([Run Time Formula Test]@row) + ":" + IF(([Run Time Formula Test]@row - INT([Run Time Formula Test]@row)) * 60 < 10, "0") + ([Run Time Formula Test]@row - INT([Run Time Formula Test]@row)) * 60

Now we have this:

And the (almost) last part is fixing the original calculation of minutes to never go above 60. We do that in a similar way to how you did the seconds in the original calculation.

This is the part we are changing:

=IF(INT([Run Time Formula Test]@row / 60) < 10, "0") + IF(([Run Time Formula Test]@row / 60) >= 1, INT(([Run Time Formula Test]@row / 60)), "0") + ":" + IF(INT([Run Time Formula Test]@row) < 10, "0") + INT([Run Time Formula Test]@row) + ":" + IF(([Run Time Formula Test]@row - INT([Run Time Formula Test]@row)) * 60 < 10, "0") + ([Run Time Formula Test]@row - INT([Run Time Formula Test]@row)) * 60

At the moment it returns the whole numbers from the minutes. Now we need it to return the whole numbers after subtracting any hours.

This formula

=INT((([Run Time Formula Test]@row / 60) - INT([Run Time Formula Test]@row / 60)) * 60)

Takes the Run Time Formula Test and divides it by 60, taking minutes and making them hours. It then subtracts the part before the decimal - the whole hours (essentially subtracting the value we have in the hours position). This results in the minutes as a decimal of hours. We multiply by 60 to get them back into minutes and use the INT of that instead of the simple INT we originally had.

This is the full formula with that part swapped:

=IF(INT([Run Time Formula Test]@row / 60) < 10, "0") + IF(([Run Time Formula Test]@row / 60) >= 1, INT(([Run Time Formula Test]@row / 60)), "0") + ":" + IF(INT([Run Time Formula Test]@row) < 10, "0") + INT((([Run Time Formula Test]@row / 60) - INT([Run Time Formula Test]@row / 60)) * 60) + ":" + IF(([Run Time Formula Test]@row - INT([Run Time Formula Test]@row)) * 60 < 10, "0") + ([Run Time Formula Test]@row - INT([Run Time Formula Test]@row)) * 60

And that gives us:

The final fix is to adjust the leading 0 in the minutes section. This was originally added (in the formula that you had already) when the minutes were less than 10. But if the minutes are 60-69 this is more than 10 so no 0 is added. But because we subtract the hours, and return 0-9 using that last change, we also need to change the leading 0 part. You can see the issue in the last 3 rows in the example.

We change this part:

=IF(INT([Run Time Formula Test]@row / 60) < 10, "0") + IF(([Run Time Formula Test]@row / 60) >= 1, INT(([Run Time Formula Test]@row / 60)), "0") + ":" + IF(INT([Run Time Formula Test]@row) < 10, "0") + INT((([Run Time Formula Test]@row / 60) - INT([Run Time Formula Test]@row / 60)) * 60) + ":" + IF(([Run Time Formula Test]@row - INT([Run Time Formula Test]@row)) * 60 < 10, "0") + ([Run Time Formula Test]@row - INT([Run Time Formula Test]@row)) * 60

To this:

=IF(INT([Run Time Formula Test]@row / 60) < 10, "0") + IF(([Run Time Formula Test]@row / 60) >= 1, INT(([Run Time Formula Test]@row / 60)), "0") + ":" + IF(INT((([Run Time Formula Test]@row / 60) - INT([Run Time Formula Test]@row / 60)) * 60) < 10, "0") + INT((([Run Time Formula Test]@row / 60) - INT([Run Time Formula Test]@row / 60)) * 60) + ":" + IF(([Run Time Formula Test]@row - INT([Run Time Formula Test]@row)) * 60 < 10, "0") + ([Run Time Formula Test]@row - INT([Run Time Formula Test]@row)) * 60

Which gives this:

Which is hopefully what you want.

We can probably make the formula more efficient, but this seemed the easiest way to explain it based on what you had. As Run Time Formula Test is used so many times you might want to retain it as a hidden column in your sheet rather than combine the formula it contains with this one. It will be easier to edit if you retain that column.

allison.worley

Thank you ALOT for all the detail above and I apologize in my delayed response.

This worked well with 2 irregularities 18:41:46 minus 18:28:36 should = 00:13:10

in this the seconds produced an extra zero = 00:13:100

this sheet had another calculation where I used the formatting of this same formula and again had an issue with the numbers being off or missing a zero. 22:00:00 minus 02:59:49 = 19:00:11 but the formula is producing 19:0:11

the first cell is calculated manually so I could check the formula. The formula produces the time savings column. Any thoughts?

KPH

It seems to be adding the leading zero if the seconds are 10 even though the logic is less than 10. Can you try changing it to less than or equal to 9 instead of less than 10?

=IF(INT([Run Time Formula Test]@row / 60) < 10, "0") + IF(([Run Time Formula Test]@row / 60) >= 1, INT(([Run Time Formula Test]@row / 60)), "0") + ":" + IF(INT((([Run Time Formula Test]@row / 60) - INT([Run Time Formula Test]@row / 60)) * 60) < 10, "0") + INT((([Run Time Formula Test]@row / 60) - INT([Run Time Formula Test]@row / 60)) * 60) + ":" + IF(([Run Time Formula Test]@row - INT([Run Time Formula Test]@row)) * 60 <= 9, "0") + ([Run Time Formula Test]@row - INT([Run Time Formula Test]@row)) * 60

Possibly because the formula produced a recurring number and multiplying by the number we divided by didn't entirely remove all the decimals.🤷‍♀️

The other 2 leading 0s seem fine with <10.