solution to "time" calculations displaying negative and positive numbers - not using 1904

[copperwasher]

Hi there Excel fans

I have a calculation requirement to display both positive and negative "time" calcs.

1904 isnt a viable solution as the sheet is shared with a Apple user...

I have the formula which works with negative numbers
=IF(H27-G27<0, "-" & TEXT(ABS(H27-G27),"hh:mm"), H27-G27)

00:45​

00:20​

-00:25​

But it returns a digital format number when the time calc is positive

00:30​

01:00​

0.020833333​

This formula (Yes, I am still learning here) returns an error:
=IF(H28-G28<0, "-" & TEXT(ABS(H28-G28),"hh:mm"), H28-G28),IF(H29-G29>0, "" & TEXT(ABS(H29-G29),"hh:mm"), H29-G29)

#VALUE!

Any suggestions would be greatly appreciated.

Yours,
Copperwasher

 

[bebo021999]

Maybe:

=IF(H27-G27<0, "-","") & TEXT(ABS(H27-G27),"hh:mm")

 

[copperwasher]

Maybe:

=IF(H27-G27<0, "-","") & TEXT(ABS(H27-G27),"hh:mm")

Maybe, indeed it has Bebo021999...

that has worked perfectly...

I can see how the formula produces the negative answer, i dont follow its process to produce a positive number...???

 

[copperwasher]

Maybe:

=IF(H27-G27<0, "-","") & TEXT(ABS(H27-G27),"hh:mm")

Ah...

Now the positive and negative time results are related to the 'text' format...

How do I sum the column of results (negative and positive numbers), which is currently stating 00:00
00:00
=SUM(I11:I252)

I have checked the format, which still produces a zero sum

Any thoughts ???

 

[bebo021999]

May be:

=IF(SUM(IF(LEFT(I11:I252)="-",-MID(I11:I252,2,255),I11:I252+0))<0,"-","")&TEXT(ABS(SUM(IF(LEFT(I11:I252)="-",-MID(I11:I252,2,255),I11:I252+0))),"hh:mm")

P/S: for Ex2016 --, must be confirmed by Ctrl-Shift-Enter combination, instead of Enter only.

 

[copperwasher]

Crikey .... no wonder I couldn't solve that, the formula is epic in length...

Thank you so much for showing me how its done