Convert Numbers to Dates

  • Thread starter Thread starter DOUG ECKERT
  • Start date Start date
D

DOUG ECKERT

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 said:
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).
Click to expand...

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
 
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?
 
I'm not sure how

010306

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

Pete
 
Pete_UK: You are correct, it should be 03-Jan-06. Do you know of a way to
covert this properly?
 
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
 
DOUG ECKERT said:
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
Click to expand...


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
 
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
 
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),MID(TEXT(A2,"000000"),3,2))
 
Pete_UK: Actually, I used the solution put forth by "teethless mama" -
Text-to-Columns Command>Next>Next>MDY. 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
 
David: Actually, I used the solution put forth by "teethless mama" -
Text-to-Columns Command>Next>Next>MDY. 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: Actually, I used the solution put forth by "teethless mama" -
Text-to-Columns Command>Next>Next>MDY. 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
 
Back
Top