Should be an easy but I can not figure it out!!

[VinceW]

I have a text box where the user enters a date and it is assigned to variable
txtDate. How to I convert that to the serial number of the date?

Thanks for the help

 

[Barb Reinhardt]

IIRC, you can use

DATEVAL(String) to get the serial date.

 

[VinceW]

Barb,
Thanks for the quick reply, however I get the error "Sub or Function not
defined" when trying that. Any suggestions?

 

[Barb Reinhardt]

My apologies. It's DateValue.

 

[VinceW]

I started with DateValue before searching for an answer here, thinking that
would do it. I have a breakpoint so I can see the value of
DateValue(txtDate) and it shows me the date of 11/16/2008 rather than the
serial number. I am really confused on this one. Thanks for your assistance
and any further suggestions.

 

[Don Guillett]

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[Don Guillett]

text box ?? Do you mean inputbox?

 

[VinceW]

Barb.....Oh my what a simple problem this really was. The DateValue is the
correct function to use. Although the date is shown as 11/16/2009 and the
code puts that in the cell requested, I simply changed the format of the cell
to be numeric ans the serial number 40133 is there just as it should be!!!!
Sometimes it is the forest and the trees syndrome!!.... Thanks for your help
and suggestions.

 

[Ron Rosenfeld]

I have a text box where the user enters a date and it is assigned to variable
txtDate. How to I convert that to the serial number of the date?

Thanks for the help

One question I have is "what do you mean by 'the serial number of the date'"?

MS stores dates using serial numbers where 1 = 1 Jan 1900 (depending on which
date system you are using).

If txtDate is a string, then to show the "serial number" you could use
something like:

clng(datevalue(txtdate))

If you mean something else by "serial number of the date", please be more
specific.

--ron

 

[VinceW]

Don,
I am using a text box on a user form for the user to enter their date.
Could not figure out how to convert the date string to a serial number.
However the DateValue function does work as long as the cell in the
spreadsheet is formatted as numeric.

Thanks for the reply

 

[Rick Rothstein]

If you don't want to always change the date to a numeric value, you can use
this to get the serial number...

SerialNumber = CDbl(CDate(Range("A1").Value))

Just change the cell reference as needed. Of course, the Range("A1").Value
part could be replaced by any String value if needed (say, from a TextBox as
an example).

 

[VinceW]

Ron,
Thanks again for another very simple solution to what I was looking for.
That also works fine. Ever have one of those days where the brain turns a
problem into something more difficult than it really is!

Thanks for your reply.

 

[Ron Rosenfeld]

Ron,
Thanks again for another very simple solution to what I was looking for.
That also works fine. Ever have one of those days where the brain turns a
problem into something more difficult than it really is!

Thanks for your reply.

You're welcome. Glad to help. And yes, I do have those days; often followed
by hours of kicking myself over the time I've lost!
--ron