Another Excel Question!

  steviegee 13:28 06 Sep 07
Locked

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 07

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 07

That doesn't work. What now please?

  VoG II 18:00 06 Sep 07

If you just want to display the month and year:

=TEXT(A1,"mmm-yy")

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

  steviegee 07:51 07 Sep 07

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

  silverous 11:28 07 Sep 07

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

01/01-03/01

=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 07

No that doesnt work that gives me 30455 or something!

  silverous 13:37 07 Sep 07

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 07

Yes I have.

  silverous 14:34 07 Sep 07

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 07

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

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

Huawei P10 review

1995-2015: How technology has changed the world in 20 years

An overview: What leading creative agencies are doing to improve diversity

New iPad, iPhone SE & Red iPhone 7 on sale now