Convert Date " yyyymm" through Formatting

[bimo]

Hi All

I have a report that shows date in this format " yyyymm"

Is there a way of converting it through the formatting feature into "mm" ?

I know about formulae =DATE(LEFT(A4,4),MID(A4,5,2), RIGHT(A4,2)) but I rather avoid adding another column..

Thank you

[Richard Buttrey]

Hi,

As always with these things we really need to see a complete example in the workbook itself. Particularly since dates are concerned.

It's not clear whether your 'date' is a proper date number or just a text string. i.e what's actually in the cell not what does it look like. Is it a number, say 41740, or text 11042014. Use =ISNUMBER(A1) to test it.

If it's a number then format it as "mm". If it's just text then you will need some function to extract the month bit of it. However upload the workbook for a more definitive response.

[Pete_UK]

Is it just year and month (no day), and with a space at the front ? If so, it will be difficult to transform it using just formatting. You could use a formula in an adjacent column to convert it to proper date format, then fix the values, then delete the original column.

Hope this helps.

Pete

[bimo]

here is the file..

[bimo]

no Space..Thanks

[Pete_UK]

Insert a new row 1 above your table. Copy row 2 into row 1 so that you have the same formatting. Put this formula in A1:

=DATE(LEFT(A2,4),RIGHT(A2,2),1)

Apply a custom format to that cell of "mmm". Then copy A1 into B1:G1 (note, you have two January columns in this sample). Then select the cells A1:G1, click <copy>, then right-click | Paste Special | Values | OK then press the <Esc> key. Then you can delete row 2.

Hope this helps.

Pete