Date anomaly

  • Thread starter Thread starter HaoHoaMastercard
  • Start date Start date
H

HaoHoaMastercard

My spreadsheet has a couple of date columns. Some of the rows would have no
date in it. However, after running the macros - which uses these cell value
as "blank", the date of "1/1/1900" appearred in those blank cells and I
could not make it disappear.

Do you have any suggestion to make that date disappear? Since I am using
VBA, I would prefer some command scripts that would turn those cells "off".

Thanks in advance.

DP
 
DP

Your code must be returning a 1 somehow which is converted to that date (Day
1 in xl = 1/1/1900, you must explicitly test for the blank cell and then
return a blank cell on that test

If Range("A1").Value="" Then
'My destination range =""
Range.Offset (0,1).Value=""
End If

--

HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.nickhodge.co.uk
blog (non tech): www.nickhodge.co.uk/blog
 
Change the format of the cell with
Range("your_cells").numberformat="dd/mm/yyyy;;"

The double semi-colons at the end mean that negative numbers and zeros
(which is what 1/1/1900 is) are not shown.
 
Hi Sam,

I thought 1/1/1900 is serial number 1. Serial number 0 would produce
1/0/1900 which really is a (qoute DP) date anomaly. Negitive numbers
give something like #########. At least, when using the regular date
system on an 'IBM compatible PC'.
After changing to the Apple (?) 1904 date system, 0 is 01-01-1904 etc.
Negative numbers give dates 'before' that date. Funny things happen.

Frans
 
Hi Nick,

I agree on the expected 1, formatted as date in the 'anomaly cells'. But
your code
puzzles me. Does it work on your computer?

Frans
 
Back
Top