Excel Date Format

  pj123 14:52 29 May 2006
Locked

I have run a Lottery Syndicate since the first draw (19/11/1994).

Once a year I download the complete Lottery history as a .csv file. I then do a "text to columns" format and save it as an .xls file.

Column A is "Drawn Date" but the date format is yyyy-mm-dd and I would like it to be dd-mm-yyyy.

I highlight the column and go to Format, Cells, Custom, and select dd/mm/yyyy and OK it but nothing changes. The date in column A still shows as yyyy-mm-dd.

How do I change this column please?

  VoG II 15:02 29 May 2006

As the 'date' has been read from a CSV file I suspect that it is being treated as text. To convert a text value in B6 to a date you can use the following formula.

=DATE(LEFT(B6,4),MID(B6,6,2),RIGHT(B6,2))

  pj123 15:17 29 May 2006

Thanks VoG™ but I got circular reference from that so no go, unless I am doing it wrong.

The first date is in A3. A1 says DrawDate.

  VoG II 15:19 29 May 2006

In a spare column on row 3 enter

=DATE(LEFT(A3,4),MID(A3,6,2),RIGHT(A3,2))

then copy the formula down.

  pj123 15:36 29 May 2006

Yep VoG™ that seems to have worked but it also seems to have merged 2 columns i and j and only shows (eg. 27/05/06 right aligned in column j) and not 27-05-2006?

Maybe I'm being too fussy?

  VoG II 15:39 29 May 2006

Try formatting the new column as Date and choosing the date format dd/mm/yyyy or format with a Custom format dd-mm-yyyy

  pj123 16:02 29 May 2006

VoG™, yes that's done it. But it still seems to be across 2 columns. I assume that to get the results in to column A I copy and paste?

  VoG II 16:06 29 May 2006

I don't understand why it should have gone across 2 columns - are you sure the column hasn't autosized to accommodate the date?

To now get those dates into Column A, copy them all, click in A3 and Edit > Paste Special, tick Values and click OK. You will need to format column A as Date. You can now delete the extra column used to convert to dates.

  pj123 16:21 29 May 2006

VoG™, yes that's done it. But it still seems to be across 2 columns. I assume that to get the results in to column A I copy and paste? Which doesn't work as the Paste area is not the same as the copy area. Why is it across 2 columns?

  VoG II 16:24 29 May 2006

I can't understand why it is across 2 columns.

Try just selecting the 'left column' and see if that works.

You need to use PasteSpecial as above or you'll generate cyclic reference errors.

  pj123 16:49 29 May 2006

VoG™, still no go. Could I possibly email you the file? Maybe you could see where I am going wrong.

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

Elsewhere on IDG sites

HP Envy x2 review: Hands-on

How Sketch and InVision have revolutionised our design workflow

The best tech gifts for Christmas 2017

Les meilleurs jeux de société (2017)