Google spreadsheet - Formula does not show negative time

1
votes

I have a google spreadsheet where I write down my working time. For example there are the following records

G9 | 05:05

H9 | 06:30

Now I want to calculate if there is time left or not. First try:

=IF(G9>0;G9-H9;0)

Second try:

=IF(G9>0;IF(G9<H9;G9-H9;H9-G9);0)

Unfortunately both ends up with a time like 22:35. Because calculation below 0 let the system think it should start by 24:00.

How can I display the value like -1:25 or vice-versa for positive times (1:05 e.g.) dynamically with a formula?

I have tried to change the format of the field, but it doesn't work since it would display all times as negative ones, even when they are positive.

EDIT:

As information if someone has troubles to work with the text values. Like @words mentioned you have to convert the value. But you also need a function if you want to work with arrays:

=SUM(ARRAYFORMULA(VALUE(I2:I32)))
1
excelgoogle-sheets

1 Answers

2
votes

One can convert time to a string with text, and prepend "-" to it when needed: 

=if(G9>=H9, text(G9-H9, "H:mm"), "-" & text(H9-G9, "H:mm"))

The result is a string, but if it's needed in later computations one can still recover the original value from it with value.