negative dates or times are displayed as # # # #
 

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 much appricated. thanks

negative dates or times are displayed as # # # #
 

Change to the 1904 date system

ToolsOptionsCalculation, and check the 1904 date system.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Amr Shehata" <Amr wrote in message
...
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 much appricated. thanks

negative dates or times are displayed as # # # #
 

Thanks Bob for reply. It worked fine but the other problem now that it did
change all other dates on the workbook to year 2002 instead of 2006.
It is the time that I need to display and not the date. Please let me know
if there is any work around it.
Kind regards,
Amr Shehata

"Bob Phillips" wrote:

Change to the 1904 date system

ToolsOptionsCalculation, and check the 1904 date system.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Amr Shehata" <Amr wrote in message
...
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 much appricated. thanks

negative dates or times are displayed as # # # #
 

you can reset them all with a simple macro

For each cell In Activesheet.UsedRange
If Isdate(cell.Value) Then
cell.Value = DateSerial(Year(cell.Value)+4, Month(cell.Value),
Day(*cell.Value)
End If
Next cell

All dates created from now on will be okay anyways.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Amr Shehata" wrote in message
...
Thanks Bob for reply. It worked fine but the other problem now that it did
change all other dates on the workbook to year 2002 instead of 2006.
It is the time that I need to display and not the date. Please let me know
if there is any work around it.
Kind regards,
Amr Shehata

"Bob Phillips" wrote:

Change to the 1904 date system

ToolsOptionsCalculation, and check the 1904 date system.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Amr Shehata" <Amr wrote in message
...
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 much appricated. thanks

Thanks
 

Thank you

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com