excel and dates

[inquirer]

Why is working with dates so frustrating in excel?

I have this code snippet:


dim sdate as date
sdate="01/01/1901"
stdate=right(sdate,4) & mid(sdate,4,2) & left(sdate,2)

In excel2002, stdate evalutes correctly to 19010101
but in excel 2003 I get an error at the stdate line because sdate at
this point is 1/01/1901.

What do I have to do to get consistency?

The point is to read in a date via a form in the format dd/mm/yyyy but
if no date is read in, set sdate to 01/01/1901. Then I want stdate to be
in the format yyyymmdd.

Is there another way to do this that will work across all versions of excel?

Thanks
Chris

 

[Mike Fogleman]

Try this:

Dim sdate As String

That way Excel won't try to force its funky date rules on the variable.

Mike F

 

[Ron Rosenfeld]

Why is working with dates so frustrating in excel?

I have this code snippet:


dim sdate as date
sdate="01/01/1901"
stdate=right(sdate,4) & mid(sdate,4,2) & left(sdate,2)

In excel2002, stdate evalutes correctly to 19010101
but in excel 2003 I get an error at the stdate line because sdate at
this point is 1/01/1901.

What do I have to do to get consistency?

The point is to read in a date via a form in the format dd/mm/yyyy but
if no date is read in, set sdate to 01/01/1901. Then I want stdate to be
in the format yyyymmdd.

Is there another way to do this that will work across all versions of excel?

Thanks
Chris

Well, sdate is a date; if you want a string representation in stdate you could
use the FORMAT function:

Dim sdate As Date
Dim stdate As String

sdate = "01/01/1901"
stdate = Format(sdate, "yyyymmdd")


--ron