Text to date

[jschping]

Hi,

I am receiving date data in the following format: MM-YY, however, for some
reason there is no first zero. So June 2009 is just 609.

How can I convert it to a date, when I don't know if I'll have 3 digits
there (like the above example) or 4 digits (for example 1109 would be
November 2009).

Thanks!

 

[timmg]

Hi,

I am receiving date data in the following format: MM-YY, however, for some
reason there is no first zero. So June 2009 is just 609.

How can I convert it to a date, when I don't know if I'll have 3 digits
there (like the above example) or 4 digits (for example 1109 would be
November 2009).

Thanks!

=right(A1,2) will separate the year
=left(A1,Len(A1)-2) will pull out the month.

You can use the Date(year,month,day) function to create a date value.

HTH,

Tim Mills-Groninger

 

[Rick Rothstein]

There are a couple of problems with your post. First, you are getting the
numbers in MYY format with **no** dash between them. Second, you are asking
how to make something like 609 into a date of June 2009; however, June 2009
is **not** a date because it does not have a day associated with it. So
there are two possible answers to your question... you just want the text
"June 2009" or you want a real date (for use in other calculations) in which
case you can specify a day (say, 1) and then format that real date to look
like June 2009. Which applies to you. Also, since you posted in a
programming newsgroup, I assume you are looking for VB code, so you need to
tell us where you are putting this "date"... in a cell or in variable.

 

[jschping]

Thanks!

You can use a formula: =TEXT(A1,"0000")

or in VBA:
dim strDate as string
strDate = Format(Range("A1"),"0000")

Sam

 

[Rick Rothstein]

Well, I guess I completely missed what you were asking for. I thought when
you asked "How can I convert it to a date...?" that you wanted an actual
date (looking like month name followed by year) and not simply a guaranteed
4-digit number.