reversing numbers to create dates
 

Hi everyone,

I receive data from an old database program like this:
19910224. I need to convert to a date format in order to use the datedif()
function.
The final product should be 24-Feb-1991 OR 1991-02-24 in date format.
Formatting like the latter example does not help. My "numbers" do not
convert to dates. All I achieve is 1991-02-24 and I can't use datedif.
Can anyone help.

TIA

Gilles Desjardins

Gilles,

Try:

=DATE(LEFT(D1,4),MID(D1,5,2),RIGHT(D1,2))

--
HTH

Sandy

Replace@mailinator with @tiscali.co.uk


"Gilles Desjardins" wrote in message
.. .
Hi everyone,

I receive data from an old database program like this:
19910224. I need to convert to a date format in order to use the datedif()
function.
The final product should be 24-Feb-1991 OR 1991-02-24 in date format.
Formatting like the latter example does not help. My "numbers" do not
convert to dates. All I achieve is 1991-02-24 and I can't use datedif.
Can anyone help.

TIA

Gilles Desjardins

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

--

Vasant



"Gilles Desjardins" wrote in message
.. .
Hi everyone,

I receive data from an old database program like this:
19910224. I need to convert to a date format in order to use the datedif()
function.
The final product should be 24-Feb-1991 OR 1991-02-24 in date format.
Formatting like the latter example does not help. My "numbers" do not
convert to dates. All I achieve is 1991-02-24 and I can't use datedif.
Can anyone help.

TIA

Gilles Desjardins

Try this:

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

This assumes your 'date' number is in A1


--
Cutter
------------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
View this thread: http://www.excelforum.com/showthread...hreadid=473167

If all those "dates" are in a single column, you could even use Data|text to
columns.

Use YMD as the field format.

Then format that column the way you want.

Gilles Desjardins wrote:

Hi everyone,

I receive data from an old database program like this:
19910224. I need to convert to a date format in order to use the datedif()
function.
The final product should be 24-Feb-1991 OR 1991-02-24 in date format.
Formatting like the latter example does not help. My "numbers" do not
convert to dates. All I achieve is 1991-02-24 and I can't use datedif.
Can anyone help.

TIA

Gilles Desjardins

--

Dave Peterson

Hi Sandy,

Brilliant! It worked!

Thank you!

gilles
"Sandy Mann" wrote in message
...
Gilles,

Try:

=DATE(LEFT(D1,4),MID(D1,5,2),RIGHT(D1,2))

--
HTH

Sandy

Replace@mailinator with @tiscali.co.uk


"Gilles Desjardins" wrote in message
.. .
Hi everyone,

I receive data from an old database program like this:
19910224. I need to convert to a date format in order to use the
datedif() function.
The final product should be 24-Feb-1991 OR 1991-02-24 in date format.
Formatting like the latter example does not help. My "numbers" do not
convert to dates. All I achieve is 1991-02-24 and I can't use datedif.
Can anyone help.

TIA

Gilles Desjardins