Date Validation in VBA for Excel/Word

[MyFlight]

HELP,

Does anyone know how I can Validate the Date using Excel/Word (VBA). What I am looking to do is if the Date is After 2/28/2004 (EXAMPLE), i want to cancel Script execution.

Also, If I Can break out the Date into Day, Month, and Year, I can then validate base on all or one of the Three.

Any and all suggestions will be greatly appreciated.

 

[zarkon4]

Look into DatePart, DateAdd, DateDiff etc.

 

[TonyJollans]

Hi MyFlight,

Not much to go on, but what about

if date > #2/28/2004# then ...

and have you tried ...

Day(date)
Month(date)
and Year(date)?


Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[VBAjedi]

To test whether a variable is a date, you can use:

If VarType(x) = vbDate Then

To convert a date that's in string form, you can use CDate:

x = "1/1/04"
y = CDate(x)

Once you know y is a valid date variable, you can make your comparison to your cutoff date. You can also then use the Month, Year, and Day functions.

Let me know if this helps!

VBAjedi

 

[FUBARinSFO]

Hi:

My usual quick and dirty method to check the validity of the date field in cleaning up input files is to put the data in a text file (.txt) and then open it with Excel. Use tab-separated data fields. What Excel can't interpret as a date shows up very clearly.

-- Roy Zider

JAN. 12, 2005 did not recognize
12-Jan-05 recognized OK
MAR. 12. 2003 did not recognize
12-Mar-03 recognized OK

 

[FUBARinSFO]

Hi again:

I checked the CDate() function a couple of days ago and it's better than Excel for extracting dates from various date strings. Use CDate() to get your input into Date type. Precede the call with an On Error Goto Err_Datetext statement pointing to your error code. CDate will throw a error number 13 'Type Mismatch' on input it can't convert.

-- Roy Zider