Date headaches

[Bill Reed]

I have a function which requests SaleDate as Date as an optional argument.
When I call the function using the function wizard, I enter 2/28/2000 as the
SaleDate. Immediately to the right of the textbox I get "= 0.00004". When
this variable gets passed to the function, it is translated, yet again, as
"12:00:03 AM". What the hell is going on here. Why can't these data types
behave?! What do I have to do to get the value I enter into the textbox in
the wizard? Dates are a recurring nightmare in VB. They never behave as one
would expect.

[Bill Manville]

In article <O0KukrMd$GA....@cppssbbsa02.microsoft.com>, Bill Reed wrote:
> When I call the function using the function wizard, I enter 2/28/2000 as the
> SaleDate. Immediately to the right of the textbox I get "= 0.00004"
>

2/28/2000 is a numerical expression (whose value is approx 0.00004, no doubt)
To enter a date as the function argument you could use
DATE(2000,2,28)

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup

[Bill Reed]

Thanks for the info. However, this also requires to user to be sophisticated
enough to outsmart the counterintuitive vagaries of VBA. What anyone would
expect, when asked to input a date, is to be able to type in something like
"2/28/00" or at least "2/28/2000" and have it be interpreted by the date
field as the date it obviously is. I don't understand why that doesn't
happen by default if the datatype of the argument is set as a Date. Nor do I
know how to configure the function so that the user does not have to have a
level of sophistication equal to that of a Microsoft MVP in order to enter a
date.

"Bill Manville" <Bill-M...@msn.com> wrote in message
news:VA.0000041...@msn.com...

[Bill Reed]

Bill,

Thanks for the clarification. However, while it is reasonable to expect a
Double data type to interpret 2/28/2000 as an arithmetical operation, it is
not equally reasonable to expect a Date data type to be performing
arithmetical operations, first and foremost. I would think that declaring a
value as a Date datatype would signal Excel to attempt to interpret the
entry as a date, according to the international settings of the pc, rather
than a calculation. I would tend to perform calculations in a Double data
type and format the result as a date. I would have expected the Date
datatype to first see if the entry could be interpreted as a date, and
convert it to serial date in the background for subsequent operations (date1
as Date - date2 as Date, etc.). If the user wants to calculate in a date
field, it is unlikely that he would have committed the serial dates to
memory (36750 - 36720). If one wanted to calculate in a date field, there
could be a convention to put all calculations in parentheses or something.
The fact is, only programmers like ourselves can comprehend that a date
field in a US application would interpret 2/28/2000 as .00004 rather than
Feb 28, 2000. At the risk of "tilting at windmills", I think that more of an
effort should be made to make things conform to the reasonable expectations
of users (non-programmers). It has always been my experience that any
solution which requires the end user to think (or, worse yet, to learn) is
problematic.

I am used to programming in Access, where I can create an input mask to
force the user to enter a date in a given format. I don't know how to
duplicate that functionality in Excel. Can you suggest a method which will
provide my users with a field that will enable them to enter a date in US
shorthand (2/28/00)? Should I be asking for the date in a String datatype?

Thanks for expert advice,

Bill

[Chip Pearson]

Bill,

The Date data type is really just a Double. The data type has nothing to
do with goes into the variable. You can put any number into a Date
variable. E.g.,

Dim D As Date
D = 123 + 456

The data type is used by other function to determine what to do with the
variable. For example,

Dim D As Date
Dim Db As Double

D = Now
Db = Now

Msgbox D
Msgbox Db

The first message box will display 2/15/2000 10:37 AM, or whatever format
the your system uses as the standard date and time format. The Msgbox
function "sees" the Date data type coming in, and formats accordingly. The
second message box will display 36571.4427199074 because Msgbox sees a
double data type, and treats it simply as a number.

The Date data type is recognized on the "outbound" direction, not the
"inbound" direction.

> I would have expected the Date
> datatype to first see if the entry could be interpreted as a date, and
> convert it to serial date in the background for subsequent operations
(date1
> as Date - date2 as Date, etc.).

Date is a data type which can simply hold a number, not an object with
properties and methods. There is no code invoked in the background when a
Date variable is used.

When you're doing something like

Dim D As Date
D = 2/28/2000

VBA is evaluating the expression before it ever "reaches" the D variable.
So even if D had some sort of processing code, the value that is being
passed is 0.0004 or whatever. The "incoming" value isn't the series of
characters 2/28/2000. It is the numeric value 0.0004.

For what you're trying to accomplish, you should stored the text from the
input box in a string variable, and use DateValue to convert to a date
serial. Something like

Dim D As Date
Dim S As String

S = InputBox("Enter A Date")

D = DateValue(S)
MsgBox D

This way, the user can input the date in any valid format (e.g.,
"2/15/2000", or "Feb 15, 2000" or "15-Feb-2000" or whatever). DateValue
will convert the string to the serial number. D will store that as a
double. Note that in the code above, Msgbox will display as a date, in the
default format (not the format that the user typed in), because it
recognizes that a Date type variable is being passed in. D doesn't store
the value in any special "date" style. It stores it as 36571.000. It is
the Msgbox function that recognizes the Date type and does the conversion.
If you declared D as a Double, rather than a Date, Msgbox would display
36571, because the variable type being passed in is a double, not a date.
The contents of a double and a date are identical.

For example, consider the following function:

Function F(D As Variant) As Variant
If TypeName(D) = "Date" Then
F = Format(D, "mm/dd/yyyy")
Else
F = Format(D, "#,##0")
End If
End Function

Then,

Dim D As Date
Dim Db As Double
D = Now
Db = D
MsgBox F(D)
MsgBox F(Db)

It tests the data type of D, and then acts appropriately. If you pass in a
Date type variable, it returns a formatted date. If you pass in a Double,
it returns a formatted number string. The contents of Db and D are exactly
the same.

Hope this is helpful.

Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting Services
www.cpearson.com ch...@cpearson.com

"Bill Reed" <ragto...@hotmail.com> wrote in message
news:uCivBC9d$GA.270@cppssbbsa05...

[Bill Reed]

Chip,

Thanks for your lucid explanation. That is the best explanation I have
gotten thus far for an otherwise incomprehensible situation.

Bill

[David McRitchie]

Well I don't see Chip's reply, but it sounds like you have some
(Lotus) transition options on. Tools --> Options --> Transition -->
(options)

HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://members.aol.com/dmcritchie/excel/excel.htm

Bill Reed <ragto...@hotmail.com> wrote in message

news:#YLpdY$d$GA.286@cppssbbsa05...

[Bill Reed]

Bill,

Thanks again for your response to my frustration. I have changed the data
type for the function argument to string, then I use the IsDate function to
evaluate it in the function's code. Chip Pearson explained to me that the
"Date" label for the Date datatype refers to how VB handles the contents of
the field after it has been typed in. In fact the Date datatype is a Double
datatype until the data comes out the other side. I guess I was thinking of
the Date datatype as being more closely related to a String than a Double,
and consequently I was surprised when the datatype performed a calculation
on what is conventionally recognized as a date. Live and Learn.

Bill

"Bill Manville" <Bill-M...@msn.com> wrote in message

news:VA.0000043...@msn.com...

> In article <uCivBC9d$GA.270@cppssbbsa05>, Bill Reed wrote:
> > It has always been my experience that any
> > solution which requires the end user to think (or, worse yet, to learn)
is
> > problematic.
> >

> <g>.
> I can't really add anything to what I said yesterday. I had a feeling ou
> would not be convinced, but I don't see that Microsoft have any options
> available for changing the current position.
>
> In order to use the function wizard in Excel you need to understand how
> expressions are evaluated. For example, if you are calling a function
MyFn
> that expects a string argument and you enter Bill as the argument it will
> create a function call
> =MyFn(Bill)
> This will give a #Name error if you don't have a Name Bill defined in your
> workbook. To pass the string "Bill" as an argument you have to enter
"Bill"
> as the argument to the function.
>
> In our date argument case, you can enter "2/28/2000" which will work with
> your regional settings, or "2000-2-28" which should work with any regional
> settings (but that may not matter to you).

>
> > I am used to programming in Access, where I can create an input mask to
> > force the user to enter a date in a given format. I don't know how to
> > duplicate that functionality in Excel. Can you suggest a method which
will
> > provide my users with a field that will enable them to enter a date in
US
> > shorthand (2/28/00)? Should I be asking for the date in a String
datatype?
> >

> You were talking about supplying dates in the function wizard - you cannot
> contrl what the user tries to supply as an argument in that context.
>
> So you may be thinking of having the date entered in some other form.
> You can use a TextBox for the user to type a string into and then you can
> test what the user typed in using the IsDate VBA function to see if it is
a
> valid date with your regional settings.

[Tim Ferguson]

On Tue, 15 Feb 2000 11:20:08 -0500, "Bill Reed"
<ragto...@hotmail.com> wrote:

>
>I am used to programming in Access, where I can create an input mask to
>force the user to enter a date in a given format.

No you can't.

Tim F

--
<!--- tim...@ferguson.netkonect.co.uk --->