Google Sheets : How can I display duration of time with a "+" or "-" prefix?

[leighhobson89]

In Google Sheets I have a column that checks another two column's and calculates the difference. I already have it conditionally formatting to be green or red depending on the difference being less than or equal to 0, and greater than 0 respectively, but I specifically want to have it display "+" if the number is greater than 0 and obviously not if it is a negative number, the negative being added automatically, unless it is zero where a sign is not needed at all (although a plus is acceptable).

I have played around with custom formats, nothing there that I could see that gives me this result, or that I could figure out.
I have tried with the concatenate and if functions, but this then destroys the formatting of the cell which is required to be a duration.

Is there a way to achieve this relatively simply?

Examples:
Comparison Value 1:
00:13:06.00
Comparison Value 2:
00:12:57.00
Output needed in Cell:
-00:00:09.00

Comparison Value 1:
00:12:57.00
Comparison Value 2:
00:13:06.00
Output needed in Cell:
+00:00:09.00

Thank You.

 

[jasonb75]

This is done in excel, but the same will apply to google sheets. You will not be able to express negative times using custom formatting due to the way that they are calculated, your example of
00:12:57.00 - 00:13:06.00 would equal 23:59:51 (9 seconds before midnight) with formatting, not 00:00:09 as required.

Note that the formula result is text, not numeric. This means that you will not be able to perform any additional calculations on it, if that is necessary then you will need 2 columns of formulas, one with the required format and another with a numeric equivalent for calculation purposes.

Book1 (version 1).xlsb
	A	B	C
1	00:13:06.000	00:12:57.000	-00:00:09.000
2	00:12:57.000	00:13:06.000	+00:00:09.000
Sheet7

Cell Formulas
Range	Formula
C1:C2	C1	=IF(A1<B1,"+","-")&TEXT(ABS(B1-A1),"hh:mm:ss.000")

 

[leighhobson89]

Thanks very much for your reply. I wanted to mention which perhaps I should have in my original question that this is supposed to show whether an elapsed time is less or more than the previous ones. In this example as the screenshots show, column T takes the value in column R on the same row, and also the best previous elapsed time found elsewhere in the sheet, (e.g. cell T7 takes R7 in screenshot 1 and H41 in screenshot 2). It compares them, and if the elapsed time in R7 is more than the previous minimum elapsed time in H41, then it should write out that elapsed time difference with a "+", and if it is less then a "-"

I take on what you said about the resulting value being a text value, but that shouldn't matter as future calculations will always be made on the columns mentioned and not on the T column, so that's one thing at least. But bearing in mind that I am talking about elapsed time and not general time, does this make it a different solution that would allow to display a "-" when it is a negative time? I couldn't figure out if your solution would work in my scenario, but I appreciate that I didn't explain it very well in the original question. The screenshots are here:

Screenshot 1 - List of times for all waypoints in the current attempt

Screenshot 2 - List of times for each waypoint taken from all attempts, and minimum extracted and then compared with benchmark to form final minimum

 

[jasonb75]

As far as I can see, it should work in that scenario.

(e.g. cell T7 takes R7 in screenshot 1 and H41 in screenshot 2).

Using the above, I think that this example formula is correct for what you need.

=IF(H41<R7,"+","-")&TEXT(ABS(R7-H41),"[h]:mm:ss")

I've changed the time format slightly so that it doesn't include 1000th's of a second, also it allows for differences greater than 24 hours, although that appears to be an unlikely requirement based on your examples.

You will not be able to use the XL2BB tool with google sheets to post an example to the board, if this doesn't work for you as needed, could you set up a dummy sheet as per your screen captures, then type in the expected results (as text so it doesn't try and coerce them into actual times) then post the link to the file here so that I can use it for testing formulas.

 

[leighhobson89]

Thank you, I was able to solve the issue with this solution!