Dispalying negative times

I Have a work sheet with two times in cells A1 and A2 in number format mm:ss.

Cell A3 callculates A2-A1.

If the value is positive is displays with no problem. However negative
values give ######## in the cell.

I have used the following to resolve.

=IF(A2-A1<0,"-"&MINUTE((A2-A1)*(-1))&":"&SECOND((A2-A1)*(-1)),A2-A1)

Is there a more elagant way to resolve.

Regards and Thanks in advance

Francis,

This dosent keep the number format so minus 1 minute comes out as
-0.00416666666666667 on screen.

Thanks for effort. looks like my original thoughs might be the only way to
display in minutes:seconds.

Regards

Francis.

"goober" wrote:

>
> =IF((A1-B1)<0,(A1-B1)*-1,A1-B1)
>
> or if you want the number to show as a negative
>
> =IF((A1-B1)<0,"-"&(A1-B1)*-1,"-"&A1-B1)
>
> I hope this is what you are looking for.
>
>
> --
> goober
> ------------------------------------------------------------------------
> goober's Profile: http://www.excelforum.com/member.php...o&userid=19838
> View this thread: http://www.excelforum.com/showthread...hreadid=486585
>
>

On Sat, 19 Nov 2005 15:16:02 -0800, "Francis Brown"
<[email protected]> wrote:

>I Have a work sheet with two times in cells A1 and A2 in number format mm:ss.
>
>Cell A3 callculates A2-A1.
>
>If the value is positive is displays with no problem. However negative
>values give ######## in the cell.
>
>I have used the following to resolve.
>
>=IF(A2-A1<0,"-"&MINUTE((A2-A1)*(-1))&":"&SECOND((A2-A1)*(-1)),A2-A1)
>
>Is there a more elagant way to resolve.
>
>Regards and Thanks in advance
>
>Francis,

Tools/Options/Calculation
Workbook Options
Select: 1904 date system


--ron

On Sat, 19 Nov 2005 20:42:34 -0500, Ron Rosenfeld <[email protected]>
wrote:

>On Sat, 19 Nov 2005 15:16:02 -0800, "Francis Brown"
><[email protected]> wrote:
>
>>I Have a work sheet with two times in cells A1 and A2 in number format mm:ss.
>>
>>Cell A3 callculates A2-A1.
>>
>>If the value is positive is displays with no problem. However negative
>>values give ######## in the cell.
>>
>>I have used the following to resolve.
>>
>>=IF(A2-A1<0,"-"&MINUTE((A2-A1)*(-1))&":"&SECOND((A2-A1)*(-1)),A2-A1)
>>
>>Is there a more elagant way to resolve.
>>
>>Regards and Thanks in advance
>>
>>Francis,
>
>Tools/Options/Calculation
> Workbook Options
> Select: 1904 date system
>
>
>--ron


If you don't want to change the date system, and don't mind having a text
string as a result, you could try this formula:

=TEXT(SIGN(E16-E17),";""-"";;")&TEXT(ABS(E16-E17),"[h]:mm")




--ron