How would I convert the number below to a date format?

010306

It should come out looking like 01-Jan-06.

(DATE and DATEVALUE functions did not seem to work as advertised).

DOUG ECKERT wrote:
> How would I convert the number below to a date format?
>
> 010306
>
> It should come out looking like 01-Jan-06.
>
> (DATE and DATEVALUE functions did not seem to work as advertised).

Something like this:

=DATE(100+RIGHT(A1,2),LEFT(A1,2),MID(A1,3,2))

Data > Text to columns > Next > Next > select Date MDY > Finish out

custom format any style you like


"DOUG ECKERT" wrote:

> How would I convert the number below to a date format?
>
> 010306
>
> It should come out looking like 01-Jan-06.
>
> (DATE and DATEVALUE functions did not seem to work as advertised).

How have you tried to use DATE and DATEVALUE, and in what respect did they
not work as advertised?
What formula did you use? What values were in the cells leading into the
formula? What result did you get? What result did you expect from that
formula, given the description of the relevant functions in Excel help?
--
David Biddulph

"DOUG ECKERT" <(E-Mail Removed)> wrote in message
news:9AD6ECFA-06BA-4D55-AE2C-(E-Mail Removed)...
> How would I convert the number below to a date format?
>
> 010306
>
> It should come out looking like 01-Jan-06.
>
> (DATE and DATEVALUE functions did not seem to work as advertised).

I'm not sure how

010306

would be interpreted as 01-Jan-06. Do you mean 01-Mar-06 or 03-Jan-06?

Pete

On Aug 13, 8:23*pm, DOUG ECKERT <DOUGECK...@discussions.microsoft.com>
wrote:
> How would I convert the number below to a date format?
>
> 010306
>
> It should come out looking like 01-Jan-06.
>
> (DATE and DATEVALUE functions did not seem to work as advertised).

Pete_UK: You are correct, it should be 03-Jan-06. Do you know of a way to
covert this properly?

"Pete_UK" wrote:

> I'm not sure how
>
> 010306
>
> would be interpreted as 01-Jan-06. Do you mean 01-Mar-06 or 03-Jan-06?
>
> Pete
>
> On Aug 13, 8:23 pm, DOUG ECKERT <DOUGECK...@discussions.microsoft.com>
> wrote:
> > How would I convert the number below to a date format?
> >
> > 010306
> >
> > It should come out looking like 01-Jan-06.
> >
> > (DATE and DATEVALUE functions did not seem to work as advertised).
>
>

David: I used the Custom Number Format to place the zero at the front of the
series and then used the formula "=DATEVALUE(A2)". I had hoped that would
convert the value to a date, but I missed something in the instructions. Is
there another way to make this series of numbers into a useable date? (We
have a text report with a comment field where a medical insurance form
renewal date is annotated. First, in MS Excel, I have to peel off extraneous
text from the cell and then convert the remaining numbers to a date. Then, I
have to subtract that number from TODAY to see how many days have elapsed
since the last renewal. So, this whole exercise is a long shot, but it will
be great if it works)...

DOUG

"David Biddulph" wrote:

> How have you tried to use DATE and DATEVALUE, and in what respect did they
> not work as advertised?
> What formula did you use? What values were in the cells leading into the
> formula? What result did you get? What result did you expect from that
> formula, given the description of the relevant functions in Excel help?
> --
> David Biddulph
>
> "DOUG ECKERT" <(E-Mail Removed)> wrote in message
> news:9AD6ECFA-06BA-4D55-AE2C-(E-Mail Removed)...
> > How would I convert the number below to a date format?
> >
> > 010306
> >
> > It should come out looking like 01-Jan-06.
> >
> > (DATE and DATEVALUE functions did not seem to work as advertised).
>
>
>

>>> "DOUG ECKERT" <(E-Mail Removed)> wrote in message
>>> news:9AD6ECFA-06BA-4D55-AE2C-(E-Mail Removed)...
>>> How would I convert the number below to a date format?
>>>
>>> 010306
>>>
>>> It should come out looking like 01-Jan-06.
>>>
>>> (DATE and DATEVALUE functions did not seem to work as advertised).
>>>
>>>
>> "David Biddulph" wrote:
>>
>> How have you tried to use DATE and DATEVALUE, and in what respect did they
>> not work as advertised?
>> What formula did you use? What values were in the cells leading into the
>> formula? What result did you get? What result did you expect from that
>> formula, given the description of the relevant functions in Excel help?
>> --
>> David Biddulph
>>
>>
>>
> DOUG ECKERT wrote:
> David: I used the Custom Number Format to place the zero at the front of the
> series and then used the formula "=DATEVALUE(A2)". I had hoped that would
> convert the value to a date, but I missed something in the instructions. Is
> there another way to make this series of numbers into a useable date? (We
> have a text report with a comment field where a medical insurance form
> renewal date is annotated. First, in MS Excel, I have to peel off extraneous
> text from the cell and then convert the remaining numbers to a date. Then, I
> have to subtract that number from TODAY to see how many days have elapsed
> since the last renewal. So, this whole exercise is a long shot, but it will
> be great if it works)...
>
> DOUG
>


Assuming all dates are 21st century, try this:

=DATE(100+RIGHT(A1,2),LEFT(RIGHT("0"&A1,6),2),MID(RIGHT("0"&A1,6),3,2))

Glenn: That was better. However, the lead zero in the original series
messed up the result. The actual cell I tested it on read "010208", but when
I pasted it to another cell (to do the manipulation), the leading zero
dropped off. I tried formatting it to show a leading zero and then used your
formula, but the result was10/20/2008 instead of 01/02/2008 as it should have
been.

('So close)!

DOUG

"Glenn" wrote:

> DOUG ECKERT wrote:
> > How would I convert the number below to a date format?
> >
> > 010306
> >
> > It should come out looking like 01-Jan-06.
> >
> > (DATE and DATEVALUE functions did not seem to work as advertised).
>
> Something like this:
>
> =DATE(100+RIGHT(A1,2),LEFT(A1,2),MID(A1,3,2))
>

Dear Teethless: That worked! Wow! I am very impressed!

Thank you.

DOUG

"Teethless mama" wrote:

> Data > Text to columns > Next > Next > select Date MDY > Finish out
>
> custom format any style you like
>
>
> "DOUG ECKERT" wrote:
>
> > How would I convert the number below to a date format?
> >
> > 010306
> >
> > It should come out looking like 01-Jan-06.
> >
> > (DATE and DATEVALUE functions did not seem to work as advertised).