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

leighhobson89

New Member
Joined
Aug 25, 2016
Messages
33
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.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,632
Office Version
  1. 365
Platform
  1. Windows
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
ABC
100:13:06.00000:12:57.000-00:00:09.000
200:12:57.00000:13:06.000+00:00:09.000
Sheet7
Cell Formulas
RangeFormula
C1:C2C1=IF(A1<B1,"+","-")&TEXT(ABS(B1-A1),"hh:mm:ss.000")
 

leighhobson89

New Member
Joined
Aug 25, 2016
Messages
33
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

1615373533324.png


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

1615374421088.png
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,632
Office Version
  1. 365
Platform
  1. Windows
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.
Excel Formula:
=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.
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,129,557
Messages
5,637,043
Members
416,955
Latest member
Gohar hussain

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top