negative dates or times are displayed as # # # #

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Amr Shehata - 08 Nov 2006 17:35 GMT

I have two cells that carry two different times as follow
cell A1 has 00:05:00
cell A2 has 00:04:00
cell A3 should display the difference in negative way(-00:01:00). I only get
##### instead of the answer in cell A3 and a message reads (negative dates or
times are displayed as # # # #) Your help will be much appricated. thanks

Reply to this Message

Bernard Liengme - 08 Nov 2006 19:00 GMT

That's what Excel does; it cannot work with negative time/dates
You could try =ABS(A2-A1) and have a flag in an adjacent cell
=IF(A2<A1,"Neg","Pos")
best wishes

Signature

Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

>I have two cells that carry two different times as follow
> cell A1 has 00:05:00
[quoted text clipped - 4 lines]
> or
> times are displayed as # # # #) Your help will be much appricated. thanks

Reply to this Message

Gord Dibben - 08 Nov 2006 21:14 GMT

This is normal.

Excel doesn't like negative dates and times.

For a workaround see John Walkenbach's site.

http://www.j-walk.com/ss/excel/usertips/tip051.htm

Gord Dibben MS Excel MVP

>I have two cells that carry two different times as follow
>cell A1 has 00:05:00
>cell A2 has 00:04:00
>cell A3 should display the difference in negative way(-00:01:00). I only get
>##### instead of the answer in cell A3 and a message reads (negative dates or
>times are displayed as # # # #) Your help will be much appricated. thanks

Reply to this Message

Amr Shehata - 09 Nov 2006 12:34 GMT

Thak you Bernard. Your idea has done the trick. Thaks for your help.
Amr

> I have two cells that carry two different times as follow
> cell A1 has 00:05:00
> cell A2 has 00:04:00
> cell A3 should display the difference in negative way(-00:01:00). I only get
> ##### instead of the answer in cell A3 and a message reads (negative dates or
> times are displayed as # # # #) Your help will be much appricated. thanks

Reply to this Message