|
I didn't see the earlier messages about this but the formulas as written should produce non-date data types in Excel. I'm assuming you want dates and not just text.
>>>=IF(LEN(B2)=6,MID(B2,3,2)&"/"&MID(B2,5,2)&"/"&MID(B2,1,2)," ")
>>>>=IF(LEN(B3=8),MID(B3,5,2)&"/"&MID(B3,7,2)&"/"&MID(B3,1,4)," ")
If you format the formula cell as a date and make the formulas look like the following, you'll have real dates that process and look like dates. Note that my formulas don't flip into a YYYY/MM/DD order. Excel (and Access) don't need or want such AS400 like treatment.
=DATEVALUE(IF(LEN(B2)=6,MID(B2,1,2)&"/"&MID(B2,3,2)&"/"&MID(B2,5,2)," "))
=DATEVALUE(IF(LEN(B3=8),MID(B3,1,2)&"/"&MID(B3,3,2)&"/"&MID(B3,5,4)," "))
The DATEVALUE function takes a mm/dd/YY or mm/dd/yyyy string input and produces a date. If will also take yyyy/mm/dd but not yy/mm/dd.
The displayed date would then show the correct 4 digit year in either case.
Steve Moland
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
