Dispalying negative times

[Guest]

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,

 

[goober]

=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.

 

[Guest]

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.

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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