Excel Date Format

  [DELETED] 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?

  [DELETED] 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))

  [DELETED] 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.

  [DELETED] 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.

  [DELETED] 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?

  [DELETED] 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

  [DELETED] 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?

  [DELETED] 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.

  [DELETED] 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?

  [DELETED] 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.

  [DELETED] 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

How to watch the World Cup for free on TV and online

Adobe's brand-new video editing app Project Rush works across your computer, tablet and phone

Best external graphics cards (eGPUs) for Mac

Les meilleurs jeux pour Android (2018)