Date format of dates stored as text

[Deutz]

Does anyone know how to return the date format of dates stored as text?

I have a column of dates delimited by commas which I split into individual strings. I then want to determine the date format for each string.

Examples of desired results:

If cell contains "301009" ---------------- then return dmy
If cell contains "12/05/09" -------------- then return dd/mm/yy
If cell contains "12/05/2009" ------------ then return dd/mm/yyyy
If cell contains "05/21/09" -------------- then return mm/dd/yy
If cell contains "Mon-10-2008" ---------- then return ddd-m-yyyy
If cell contains "12 November 2009" ----- then return dd mmmm yyyy
<TABLE id=table1 cellSpacing=0 cellPadding=2 width="50%" border=0><TBODY><TR vAlign=top><TD align=left></TD><TD align=left></TD></TR><TR vAlign=top><TD align=left>Thanks for you assistance

Michael
</TD><TD align=left></TD></TR><TR vAlign=top><TD align=left></TD><TD align=left></TD></TR><TR vAlign=top><TD align=left></TD><TD align=left></TD></TR></TBODY></TABLE>

 

[jbeaucaire]

1) Strings stored as text don't have a "date format". They are strings. It's up to the human mind to interpret what the strings mean.

Even in your example above you contradict "simple" logic with examples two and four...those two text strings are identical in layout, but you indicate they should return different "date formats".

Discerning between those formats is easy for humans, but not to a formula or macro. If a text string says 5/3/09 and it's stored as text, a human has to determine if that's May3rd or March5th.

2) You can convert most of those text strings INTO actual dates with a TEXT TO COLUMNS trick, but as you've already noted, they aren't consistent:

Excel Workbook
	A	B
1	Before	After
2
3	301009	10/30/2009
4	12/05/09	5/12/2009
5	12/05/2009	5/12/2009
6	05/21/09	5/21/2009
7	Mon-10-2008	Mon-10-2008
8	12 November 2009	12-Nov-09
Sheet3

Steps:
a) Highlight column of values
b) Select Data > Text To Columns > Delimited > Next > Next
c) Select Date: in the upper right corner "Column Data Format" and select DMY
d) Click OK

 

[Deutz]

Thanks for the reply.

I see what you mean about interpreting strings different ways and the limits of what Excel can do in that respect.

For those strings that can be converted into dates, how would I then flag via VBA or formula what date format they are in?