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,

=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

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"
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
wrote:

On Sat, 19 Nov 2005 15:16:02 -0800, "Francis Brown"
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