negative dates or times are displayed as # # # #

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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)
 
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
 
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)
 
Back
Top