Date formatting in excel

[tatiana131]

Hello,

I get huge amounts of data formatted with the date as 2011-03 all in one cell. I need to convert that 2011-03 into 20110331 - i.e. yyyy mm last day of month. Right now we are all doing this manually, for some reason, which takes hours.

Is there a formula that would accomplish this? I found an old thread with [FONT=&quot]=text(c1,”emmd”) as a suggestion, but that just gives me a #NAME? error message.

Thanks.
[/FONT]

 

[AlphaFrog]

When you select one of the dates, is the value as seen in the formula bar a date like mm/dd/yyyy or dd/mm/yyyy or is it still yyyy-mm

 

[tatiana131]

It's still yyyy-mm. They are dumped out of the accounting system as a text cell.

 

[MARK858]

Try

=TEXT(EOMONTH(--(RIGHT(C1,LEN(C1)-FIND("-",C1))&"/"&LEFT(C1,4)),0),"yyyymmdd")

 

[tatiana131]

That totally worked!!!! Thank you bunches. Not the most concise formula, but it gets the job done.

You rock.

 

[AlphaFrog]

Another one for what it's worth...

=TEXT(EDATE(A1&"-01",1)-1,"yyyymmdd")

 

[tatiana131]

That one works like a charm too - thank you - it's always good to have more than one.

 

[tatiana131]

This forum has literally saved hours of wasted time for us - very grateful.