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

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 :

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

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.

In article ,
OSPAM (JimFor) wrote:

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?

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

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.


On 21 Nov 2004 02:56:47 GMT, OSPAM (JimFor) wrote:

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

Thanks for your help.