VBA Date Format

[JimFor]

Hi,

How do you set up a cell format so that VBA accepts and reads the input as
being a date in the fomat looking like this: 12/12/04 (mm/dd/yy)?. And will
the program be able to determine things like the fact that one date is earlier
than another?
Thanks

 

[keepITcool]

Assuming the cell contains a NUMBER(dateserial)
formatted as date:

Sub foo()
Dim dtTest As Date
dtTest = CDate(Range("a1").Value2)

Range("a2") = DateSerial(Year(dtTest), Month(dtTest) + 1, Day(dtTest))
Range("a2").NumberFormat = "mm/dd/yy"
End Sub



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


JimFor wrote :

 

[JE McGimpsey]

If your cell has a Date format, just assign the cell's value to a
variable. For instance:

Dim dtMy_Date As Date
dtMy_Date = Range("A1").Value

That works regardless of which date system you're using (i.e., 1900 or
1904). OTOH, if your cell is not formatted as a date the above code will
covert the value to a date, but only if it can coerce to a date (i.e.,
'11/20/2004 will work, while "hello" would give you a type mismatch),
and only in the 1900 date format.

 

[JimFor]

OK. Suppose you have two sources of dates. One is user input. The other is
from an Excel file which was loaded into a worksheet. I assume I format the
cell which receives the user input using your techniques. What I now have to
do is to compare the user input date with the dates on, say, Sheet1. If a date
on Sheet1 is chronically before the date the user enters, the program does one
thing. If it is after, it does another. Can the program distinguish that? And
is it safe to say if I formatted the dates which were loaded into the sheet
using the mm/dd/yy format, the program will recognize the format. Or do I
have to set the format again when I set up the sheet.

Thanks

 

[Myrna Larson]

If Excel recognizes the input as a date, it is translated to a number,
specifically the number of days that have elapsed since Dec 31, 1899. When
comparing dates, Excel is comparing these numbers. The cell format is
irrelevant.