Date problems

[codonnell73]

I've reviewed several problems related to date formatting and couldn't get this working. Help appreciated. I'e attached a sample excel of the data I'm trying to manipulate. I want to be able to extract the MM,YYYY from column A in a new column extract the MM,YYYY from column B in a second new column. I cant use TEXT(A2,"YYYY-MM") or variations of this and I'm sure it's related to the format of the source fields (they're not dates). I assume I need an interim field to perform a conversion?C Any help/suggestions appreciated!

[JieJenn]

=month(a2)&","&year(a2) ?

[dflak]

They seem to be coming across as dates in the download. If they are strings that look like dates in your originals, then wrap them in DATEVALUE() before trying to use TEXT() on them: =TEXT(DATEVALUE(A2),"mm-yyyy").

[Vraag en antwoord]

=TEXT(DATE(MID(A2;7;4);LEFT(A2;2);MID(A2;4;2));"yyyy-mm")

[codonnell73]

I get a #VALUE! error in the cell when I use these expressions:

=month(a2)&","&year(a2)
=TEXT(DATEVALUE(A2),"mm-yyyy")

and a formula error dialogue with the formula:

=TEXT(DATE(MID(A2;7;4);LEFT(A2;2);MID(A2;4;2));"yyyy-mm")

Thanks for the quick help but still stuck

[dflak]

Try =if(ISNUMBER(A2), TEXT(A2,"mm-yyyy"),TEXT(DATEVALUE(A2),"mm-yyyy")).

The issue I have is I can't duplicate the problem from the attachment you sent.

[codonnell73]

Thanks for your help. =if(ISNUMBER(A2), TEXT(A2,"mm-yyyy"),TEXT(DATEVALUE(A2),"mm-yyyy")) resulted in #VALUE, but I did get =TEXT(DATE(MID(A6,7,4),LEFT(A6,2),MID(A6,4,2)),"yyyy-mm") to work. Thanks for the help!