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

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

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

"Leigh" wrote in message
...

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

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.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Leigh" wrote in message
...

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

Another way would be to use datatext 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




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

"Leigh" wrote in message
...

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

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

"Biff" wrote:

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

"Leigh" wrote in message
...

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

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

"Peo Sjoblom" wrote:

Another way would be to use datatext 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




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

"Leigh" wrote in message
...

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

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

"Ragdyer" wrote:

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.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Leigh" wrote in message
...