I have dates being imported (AS TEXT) to excel in various formats and
want them all to appear in their cells in the same 8 digit format
(MMDDYYYY) so it can be exported to another program

for example:

Single Digit Month and Day
cell A1: 3179 (convert to 03011979)
cell A2: 30179 (convert to 03011979)
cell A3: 030179 (convert to 03011979)
cell A4: 311979 (convert to 03011979)
cell A5: 3011979 (convert to 03011979)
cell A6: 03011979 (already correct)

Single Digit Month and Double Digit Day
cell A7: 52165 (convert to 05211965)
cell A8: 052165 (convert to 05211965)
cell A9: 5211965 (convert to 05211965)
cell A10: 05211965 (already correct)

Double Digit Month and Single Digit Day
cell A11: 12523 (convert to 12051923)
cell A12: 120523 (convert to 12051923)
cell A13: 1251923 (convert to 12051923)
cell A14: 12051923 (already correct)

Double Digit Month and Double Digit Day
cell A15: 112180 (convert to 11211980)
cell A16: 11211980 (already correct)


--
jermsalerms
------------------------------------------------------------------------
jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167
View this thread: http://www.excelforum.com/showthread...hreadid=500884

I don't think you'll ever find a way to do it perfectly.

If all you're doing is looking at numbers, then you can't tell what this should
mean:

12523

Dec 5th, 1923 or Jan 25, 1923.




jermsalerms wrote:

I have dates being imported (AS TEXT) to excel in various formats and
want them all to appear in their cells in the same 8 digit format
(MMDDYYYY) so it can be exported to another program

for example:

Single Digit Month and Day
cell A1: 3179 (convert to 03011979)
cell A2: 30179 (convert to 03011979)
cell A3: 030179 (convert to 03011979)
cell A4: 311979 (convert to 03011979)
cell A5: 3011979 (convert to 03011979)
cell A6: 03011979 (already correct)

Single Digit Month and Double Digit Day
cell A7: 52165 (convert to 05211965)
cell A8: 052165 (convert to 05211965)
cell A9: 5211965 (convert to 05211965)
cell A10: 05211965 (already correct)

Double Digit Month and Single Digit Day
cell A11: 12523 (convert to 12051923)
cell A12: 120523 (convert to 12051923)
cell A13: 1251923 (convert to 12051923)
cell A14: 12051923 (already correct)

Double Digit Month and Double Digit Day
cell A15: 112180 (convert to 11211980)
cell A16: 11211980 (already correct)

--
jermsalerms
------------------------------------------------------------------------
jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167
View this thread: http://www.excelforum.com/showthread...hreadid=500884

--

Dave Peterson

Didn't catch that scenario!


The problem is that I am uploading the spreadsheet as a .csv to a
program that fills in the Date of Birth field and it will only
recognize 8 digit format.

Also it is not something that can be done by hand because I get a list
of 4000+ once a month.

Any suggestions for accuracy on a majority of them


--
jermsalerms
------------------------------------------------------------------------
jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167
View this thread: http://www.excelforum.com/showthread...hreadid=500884

I think I'd add an extra column:

With the data in column A and my extra column B:

=len(a1)
and drag down.

Then I'd sort by this column.

Now I could use data|text to columns for each group of non-ambiguous entries.
(4 digits or 6 digits only???)

Since excel is gonna guess 2023 instead of 1923, you may want to convert the
year using another column:

=date(year(c1)-if(Year(c1)2006,100,0),month(c1),day(c1))

But I think you're gonna get stuck fixing lots manually.



jermsalerms wrote:

Didn't catch that scenario!

The problem is that I am uploading the spreadsheet as a .csv to a
program that fills in the Date of Birth field and it will only
recognize 8 digit format.

Also it is not something that can be done by hand because I get a list
of 4000+ once a month.

Any suggestions for accuracy on a majority of them

--
jermsalerms
------------------------------------------------------------------------
jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167
View this thread: http://www.excelforum.com/showthread...hreadid=500884

--

Dave Peterson

On Thu, 12 Jan 2006 18:01:17 -0600, jermsalerms
wrote:


Didn't catch that scenario!


The problem is that I am uploading the spreadsheet as a .csv to a
program that fills in the Date of Birth field and it will only
recognize 8 digit format.

Also it is not something that can be done by hand because I get a list
of 4000+ once a month.

Any suggestions for accuracy on a majority of them

I think you will need to change the data entry method to produce consistency.
--ron

dates are filling in via these different formats in cells A1:A901

MDYY
MMDYY
MDDYY
MMDDYY
MDYYYY
MDDYYYY
MMDDYYYY

I need to convert them all to MMDDYYYY in cell B1:B901

so far no luck figuring this out


--
jermsalerms
------------------------------------------------------------------------
jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167
View this thread: http://www.excelforum.com/showthread...hreadid=500884

As soon as you can specify what "12523" is, then the rules could be applied that
way.

But, again, you won't always get the correct result. They'll look like dates,
but they may not be the correct dates.

jermsalerms wrote:

dates are filling in via these different formats in cells A1:A901

MDYY
MMDYY
MDDYY
MMDDYY
MDYYYY
MDDYYYY
MMDDYYYY

I need to convert them all to MMDDYYYY in cell B1:B901

so far no luck figuring this out

--
jermsalerms
------------------------------------------------------------------------
jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167
View this thread: http://www.excelforum.com/showthread...hreadid=500884

--

Dave Peterson

On Fri, 13 Jan 2006 15:35:18 -0600, jermsalerms
wrote:


dates are filling in via these different formats in cells A1:A901

MDYY
MMDYY
MDDYY
MMDDYY
MDYYYY
MDDYYYY
MMDDYYYY

I need to convert them all to MMDDYYYY in cell B1:B901

so far no luck figuring this out

And you will NOT be able to figure it out because the data is inherently
ambiguous.

I repeat, YOU WILL HAVE TO STANDARDIZE THE DATA ENTRY METHOD.

There is no method of deducing the input format from the resultant number,
given the allowable formats.
--ron