General Number field has 19820431. How to convert to 04/31/1982?

[Biff]

General Number field has 19820431. How to convert to 04/31/1982?

I'm afraid that would be impossible since April only has 30 days!

As long as there is always a 4 digit year, 2 digit month and 2 digit day:

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

Biff

 

[Ragdyer]

If all your data is in the same format as you posted in your example, you
can use TTC (TextToColumns).
You should realize however, that April has *only* 30 days.

Select your column of data, then,
<Data> <TextToColumns> <Next> <Next>
Click on "Date", and make sure that
YMD (your *present* configuration)
shows in the date box.
Then <Finish>.

 

[Peo Sjoblom]

Another way would be to use data>text to columns, click next twice then
under column data format select date and YMD, won't of course work with this
particular number due to the 31st April but for reasonable dates it would
<g>

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon

 

[Leigh]

Yes, this is exactly what I need. Thanks a ton.

I'm afraid that would be impossible since April only has 30 days!

As long as there is always a 4 digit year, 2 digit month and 2 digit day:

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

Biff

 

[Leigh]

Yes, I tried both Peo and Biff's suggested and they both work. Saved a bunch
of time. Thanks.

 

[Leigh]

Yes, and you gave perfect easy to follow instructions. Worked like a charm!

If all your data is in the same format as you posted in your example, you
can use TTC (TextToColumns).
You should realize however, that April has *only* 30 days.

Select your column of data, then,
<Data> <TextToColumns> <Next> <Next>
Click on "Date", and make sure that
YMD (your *present* configuration)
shows in the date box.
Then <Finish>.

[/QUOTE]