RE: Excel and Dates

[Steve Moland <Steve@xxxxxxxxxxx>]

Title: RE: Excel and Dates

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