Another Excel Question!

  steviegee 13:28 06 Sep 2007

I have a column with dates in. I am using text to columns to split them up. Pre 2000 is no problem i.e 03/97-02/99 goes to Mar-03 and Feb-99 which is what I want. Problems start after that 01/01 goes to Jan-07 instead of Jan-01 and so on 06/03-12/04 goes to Mar-07 and Apr-07 instead of Jun-03 and Dec-04!

What can I do to make them all uniform.

  Jasper1973 14:22 06 Sep 2007

If you right click on the column and format it as "Date" and select "Mar-98" option this will allow you to type a date in ful ie 01/03/02 and it will show the date as Mar-02.

Hope that helps!

  steviegee 16:15 06 Sep 2007

That doesn't work. What now please?

  VoG II 18:00 06 Sep 2007

If you just want to display the month and year:


where A1 contains a value that Excel recognises as a date.

  steviegee 07:51 07 Sep 2007

That doesn't seem to work, it gives the same that is in the cell.

  silverous 11:28 07 Sep 2007

Try this formula in cell B1, assuming that A1 contains for example:


=VALUE("01/" & LEFT(A1,5))

You'd then have to copy this down alongside any entries in column A that you wanted processed in this way.

  steviegee 12:19 07 Sep 2007

No that doesnt work that gives me 30455 or something!

  silverous 13:37 07 Sep 2007

Have you got the cell B1 formatted correctly? i.e. as Jan-01 or whatever? (Format, Cell, Date, then choose the relevant format).

  steviegee 13:58 07 Sep 2007

Yes I have.

  silverous 14:34 07 Sep 2007

Works fine for me, must be something wrong. If I put the text:

01/01-03/01 in cell A1.
Then put the formula above in B1
I initially get: 36892
I then do Format, Cell, Number and choose Date.
I pick the format Mar-98 and click OK.
Cell B1 then shows Jan-01. As required?

  silverous 14:34 07 Sep 2007

The format is on B1 not A1, not sure if that helps.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Fujitsu Lifebook P727 laptop review

Converse's new logo: the trainer brand looks to its heritage for a fresh identity

Mac power user tips and hidden tricks

Comment lancer Windows 10 en mode sans ├ęchec ?