negative dates or times are displayed as # # # #

[Amr Shehata]

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

 

[Bob Phillips]

Change to the 1904 date system

Tools>Options>Calculation, and check the 1904 date system.

--
HTH

Bob Phillips

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

 

[Amr Shehata]

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]

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]

Thank you

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