Dispalying negative times

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Francis Brown - 19 Nov 2005 23:16 GMT

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,

Reply to this Message

goober - 19 Nov 2005 23:50 GMT

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

Signature

goober

Reply to this Message

Francis Brown - 20 Nov 2005 00:06 GMT

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.

> =IF((A1-B1)<0,(A1-B1)*-1,A1-B1)
>
[quoted text clipped - 3 lines]
>
> I hope this is what you are looking for.

Reply to this Message

Ron Rosenfeld - 20 Nov 2005 01:42 GMT

>I Have a work sheet with two times in cells A1 and A2 in number format mm:ss.
>
[quoted text clipped - 12 lines]
>
>Francis,

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

--ron

Reply to this Message

Ron Rosenfeld - 20 Nov 2005 02:09 GMT

>>I Have a work sheet with two times in cells A1 and A2 in number format mm:ss.
>>
[quoted text clipped - 18 lines]
>
>--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

Reply to this Message