Date validation with abiguous format

[nate.kolman]

Hi all,

I'm working on developing a date validation where the user enters into
a cell a date in the format "yyyymmdd". The validation must happen
after the entry, so I'm not able to use a simple validation function,
or even cell formatting (due to copying/pasting into the cell). The
code has to recognize that this "number" is a valid date. So far I've
defined a variable that extracts the text of this number into a more
friendly date format and then decides if it's a valid date.

Dt = Mid(C.Text, 5, 2) & "/" & Right(C.Text, 2) & "/" & Left(C.Text, 4)
If Dt.... End If

Is there an easier way to do this? Is there a function that says - if
this number in "yyyymmdd" format is a valid date, then do this... ?

Thanks,
Nate

 

[Guest]

If you are checking the date format, then and assuming that the date is
entered in cell "A1":

If Range("$A$1").NumberFormat = "m/d/yyyy" (or whatever format you want) Then
Keep going
Else
Do something else
End If

If you are looking for a specific date that can be measured in days from
todays date then assuming 30 days from today:

If Range("$A$1").Value = Now()+30 Then
Do something
Else
Do something else
End If

Maybe this will give you some ideas.

 

[Nate]

Thanks, but...

When the number is entered into the cell, the formatting of the cell is
"general" so the .numberformat check won't work. So today (11/11/06)
would be recognized as 20,061,111. Likewise, this number is not an
excel date serial number, so the .value statement won't work either.
Unfortunately, these are the constraints of the project so the cells
can allow for copying/pasting and then doing all the data validation
after the fact. The way I'm doing it with the text conversion
(mid,right,left) is working, but thought there might be an easier way.

Nate

 

[Bob Phillips]

Dim sDate As String
sDate = 20061111
MsgBox IsDate(Left(sDate, 4) & "-" & Mid(sDate, 5, 2) & "-" & Right(sDate,
2))


--
HTH

Bob Phillips

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

 

[Nate]

Thanks all!

I found that using the VBA function "dateserial" is going to work the
best. This will convert the text into an Excel date serial number and
I can use that for evaluating.

Dt = DateSerial(Left(C.Text, 4), Mid(C.Text, 5, 2), Right(C.Text, 2))

Thanks,
Nate