subtract times with a negative result

[vdmbqb]

I am using Office 2007.

I need a formula subtracting one time from another that can produce a
negative amount. The cells are formatted custom h:mm.

For instance Cell A1=6:30 Cell B1=6:45

If you subtract 6:45 from 6:30 I need a result of -0:15 (negative)

All I get is #########

Any help would be greatly appreciated.

 

[Dave Peterson]

You have a few of choices.

One keep the value in that cell (still showing ####), but use another cell to
format it nicely:

=if(c1<0,"-","")&text(abs(c1),"hh:mm")

Or you could change the formula to return text:

=if(a1<b1,"-","")&text(abs(a1-b1),"hh:mm")

Or you could change the base date to 1904. Then you can show negative
dates/times.

But this will change the existing dates in your workbook and make sharing data
between workbooks more hazardous:

(saved from a previous post)

You could use the 1904 base date
(in xl2003 menus)
Tools|Options|calculation tab|check "1904 date system"

Be aware that your dates will now be off by 4 years and one day.

And copying data (dates and times) between workbooks with different base dates,
will be a big problem.

One way to add (or subtract) those four years back is to find an empty cell, put
1462 into that cell.

Copy that cell.

Select your range that contains the dates. Edit|PasteSpecial|click Add (or
subtract) (in theoperation box) and check values.

You may want to do it against a copy...just in case.

Most windows users use 1900 as the base date. Mac users (mostly??) use 1904 as
the base date.