null date?

[John]

1. On the worksheet a cell is formated as date.
In vb is a variable also formated as date
If I have variable = cell I get a formating error if the cell is empty
I can use isempty or isdate first to test the cell but seems like a pain

2. cell is formated as date like 01/01/09
If someone enters the date in the cell as 1/01/09 then get an error if
try to set the cell = variable formated as date.

In general I'm having trouble working with dates I guess. In the program
data is entered directly into the worksheet by the operator.

Thanks

John

 

[Harald Staff]

Hi John

A date is simply X days since new year to 1900. Try this:
Enter 40000 into a cell.
Format it as any date format. The formula bar will also show a date, in the
same or a different date format. Format the cell as another date format, the
formula bar shows the same as before.
Meaning a date is a date, formatting is for cell display only.

What may cause trouble is that cells can contain text that looks like dates,
but are just text. Then the formula bar does not display its normal date
format, and the text gets left aligned in stead of default rigth.

Try running this when in an empty cell, no error:

Sub test()
Dim D As Date
D = ActiveCell.Value
MsgBox Format(D, "yyyy.mm.dd")
End Sub

HTH. Best wishes Harald

 

[Bernie Deitrick]

John,

try to set the cell = variable formated as date

Try using this if your variable is a string

Dim variable As String
variable = "1/1/9"
ActiveCell.Value = DateValue(variable)


or, if it is a date

Dim variable As Date
variable = DateValue("1/1/9")
ActiveCell.Value = variable


HTH,
Bernie