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" wrote in message
...
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
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
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" wrote in message
...
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 in message
...
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).

Doug,

if you have always got 6 digits with a leading zero when required,
then you can do it like this:

=DATE("20"&RIGHT(A1,2),LEFT(A1,2),MID(A1,3,2))

This will ensure the dates are in this century, but you can omit the
"20"& if you are happy with the setting in Windows (i.e. 00-29
interpreted as 2000-2029, and 30-99 as 1930-1999, by default).

Hope this helps.

Pete

On Aug 14, 5:19*pm, DOUG ECKERT
wrote:
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
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).- Hide quoted text -

- Show quoted text -

In my version of Excel (2003), the help for DATEVALUE makes it clear that
the text string being input into DATEVALUE has to be in one of the forms
which Excel will recognise as a date, such as "1/30/2008" or "30-Jan-2008"
or 22-AUG-2008" or "2008/02/23" or "5-JUL".
You have given it something that looks like a number, not like a date.

In other answers you have been given a number of answers.
The Data/ Text to Columns option is often the best.

If you particularly wanted to feed your existing number (which you have
recognised doesn't actually contain the leading zero) into the DATEVALUE
formula you could use
=DATEVALUE(LEFT(TEXT(A2,"000000"),2)&"/"&MID(TEXT(A2,"000000"),3,2)&"/"&RIGHT(A2,2))
but you need to remember that anything that looks at a mm/yy/dd format
relies on Windows Regional Setting (not Excel settings) to distinguish it
from dd/mm/yy.
A simpler way, avoiding the use of DATEVALUE, is simply to use
=--TEXT(A2,"00\/00\/00"), but it suffers from the same dependence on
regional settings.

The problem you had with Glenn's formula and your loss of leading zeroes
could be addressed by
=DATE(100+RIGHT(A2,2),LEFT(TEXT(A2,"000000"),2),MI D(TEXT(A2,"000000"),3,2))
--
David Biddulph

"DOUG ECKERT" wrote in message
...
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" wrote in message
...
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: Actually, I used the solution put forth by "teethless mama" -
Text-to-Columns CommandNextNextMDY. That flipped the dates into the
proper format. However, I am going to keep your input for future reference.
The issue of leading zeros is certain to rear its ugly head again some day in
another context.

Thank

"Pete_UK" wrote:

Doug,

if you have always got 6 digits with a leading zero when required,
then you can do it like this:

=DATE("20"&RIGHT(A1,2),LEFT(A1,2),MID(A1,3,2))

This will ensure the dates are in this century, but you can omit the
"20"& if you are happy with the setting in Windows (i.e. 00-29
interpreted as 2000-2029, and 30-99 as 1930-1999, by default).

Hope this helps.

Pete

On Aug 14, 5:19 pm, DOUG ECKERT
wrote:
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
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).- Hide quoted text -

- Show quoted text -

David: Actually, I used the solution put forth by "teethless mama" -
Text-to-Columns CommandNextNextMDY. That flipped the dates into the
proper format. However, I am going to keep your input for future reference.
The issue of leading zeros is certain to rear its ugly head again some day in
another context.

Thank you very much!

DOUG

"Glenn" wrote:

"DOUG ECKERT" wrote in message
...
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: Actually, I used the solution put forth by "teethless mama" -
Text-to-Columns CommandNextNextMDY. That flipped the dates into the
proper format. However, I am going to keep your input for future reference.
The issue of leading zeros is certain to rear its ugly head again some day in
another context.

Thank you very much.

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))

You're welcome - glad you found something that worked for you.

Pete

On Aug 14, 9:08*pm, DOUG ECKERT
wrote:
Pete_UK: *Actually, I used the solution put forth by "teethless mama" -
Text-to-Columns CommandNextNextMDY. *That flipped the dates into the
proper format. *However, I am going to keep your input for future reference.
The issue of leading zeros is certain to rear its ugly head again some day in
another context.

Thank