Another Excel Problem - Date Formats

[Dave]

My Excel woes continue

I've got a column of dates in a spreadsheet that are recorded in at least three formats (dd.mm.yyyy, dd/mm/yyyy and dd-mm-yyyy). I need them all to be formatted as ddmmyyyy.

Is there a (preferably non-macro) way of stripping out the dots, slashes and dashes?

(We could do them by hand, I suppose, but there are >2000 of them.)

[spen666]

My Excel woes continue

I've got a column of dates in a spreadsheet that are recorded in at least three formats (dd.mm.yyyy, dd/mm/yyyy and dd-mm-yyyy). I need them all to be formatted as ddmmyyyy.

Is there a (preferably non-macro) way of stripping out the dots, slashes and dashes?

(We could do them by hand, I suppose, but there are >2000 of them.)

Use Fiind/ Replace

Replace"." with"" and then replace "/" with ""  omitting the "" each time should do it

[nuttycyclist]

My Excel woes continue

I've got a column of dates in a spreadsheet that are recorded in at least three formats (dd.mm.yyyy, dd/mm/yyyy and dd-mm-yyyy). I need them all to be formatted as ddmmyyyy.

Is there a (preferably non-macro) way of stripping out the dots, slashes and dashes?

(We could do them by hand, I suppose, but there are >2000 of them.)

Highlight the column, then on the menu : Format -> Cells and select date and a suitable format (you can go custom and type ddmmyyyy into the box)

[Dave]

My Excel woes continue

I've got a column of dates in a spreadsheet that are recorded in at least three formats (dd.mm.yyyy, dd/mm/yyyy and dd-mm-yyyy). I need them all to be formatted as ddmmyyyy.

Is there a (preferably non-macro) way of stripping out the dots, slashes and dashes?

(We could do them by hand, I suppose, but there are >2000 of them.)

Highlight the column, then on the menu : Format -> Cells and select date and a suitable format (you can go custom and type ddmmyyyy into the box)

YES! Thank you, thank you, thank you

[gonzo]

Use a combination of Spen and Nutty's method.

First, use find/replace to change the dividing symbols to:
/
then excel will be able to read every one of the dates as a date (it doesn't recognise dates separated with full stops)

Once you've done that, highlight the cells, press: crtl+1

Make sure the number tab is highlighted at the top

In the category column, select custom

In the top white box type:
ddmmyyyy

If you want to add in extra characters, put them in inverted commas. Say you wanted to put the format in dd.mm.yyyy format you'd type:
dd"."mm"."yyyy

[nuttycyclist]

If you actually look at it Excel stores the dates as a number, so the formatting is irrelevant.  Therefore, unless the data has been entered as text, there is no need to mess around with find and replace.

[Hello, I am Bruce]

Excel is extremely tricky!

You need to be aware that every cell has an underlying value and a formatting method used to display it.

Two cells that look the same could have different underlying values and different formatting methods.

Two cells with the same underlying value and different formatting methods can look completely different.

If you have a cell that looks like 01/01/2008, then it could either be the number 39448.00 formatted as a date.  Or it could be the text string "01/01/2008" formatted as text.

If Excel knows that all your data are dates and is just formatting then in different ways, then you just need to change the formatting.  No problem.

But if you have a mixture of actual dates and text strings that people interpret as dates, then you will have to be very careful.  I can't think of a reliable way to do this (everything I've experimented with has had cases that can break it).



I'm very worried about your choice of ddmmyyyy as a format for dates.  If you type something in this format then Excel will interpret it as a number.  This will completely screw up any calculations involving dates (because the number 01012008 is interpreted as 12th Oct 4670).  Using a day-first formal will also make sorting into chronological order difficult.  If you don't have an external constraint that needs this format, then don't use it.

Try this exercise:
 - create a new spreadsheet
 - select the first cell (A1)
 - bring up the format menu, pick custom, and type in ddmmyyyy
 - type the date 01012008 into A1
 - What does it say in the cell?

[peterh]

If you actually look at it Excel stores the dates as a number, so the formatting is irrelevant.  Therefore, unless the data has been entered as text, there is no need to mess around with find and replace.

Excel will try and second guess you if it thinks you are entering a date.  However, if it's entered like 23.12.2007 then excel will store it as text so you could end up with a mix of data as well as formats.

[Dave]

Excel is extremely tricky!

You need to be aware that every cell has an underlying value and a formatting method used to display it.

Two cells that look the same could have different underlying values and different formatting methods.

Two cells with the same underlying value and different formatting methods can look completely different.

If you have a cell that looks like 01/01/2008, then it could either be the number 39448.00 formatted as a date.  Or it could be the text string "01/01/2008" formatted as text.

If Excel knows that all your data are dates and is just formatting then in different ways, then you just need to change the formatting.  No problem.

But if you have a mixture of actual dates and text strings that people interpret as dates, then you will have to be very careful.  I can't think of a reliable way to do this (everything I've experimented with has had cases that can break it).



I'm very worried about your choice of ddmmyyyy as a format for dates.  If you type something in this format then Excel will interpret it as a number.  This will completely screw up any calculations involving dates (because the number 01012008 is interpreted as 12th Oct 4670).  Using a day-first formal will also make sorting into chronological order difficult.  If you don't have an external constraint that needs this format, then don't use it.

Try this exercise:
 - create a new spreadsheet
 - select the first cell (A1)
 - bring up the format menu, pick custom, and type in ddmmyyyy
 - type the date 01012008 into A1
 - What does it say in the cell?

Thankfully, this hasn't turned out to be a problem. Excel is behaving itself and has decided that all the values in the column are dates, so Nutty's suggestion works fine. More by luck than judgement, granted.

We need the data as ddmmyyyy as that's the format in which it needs to be exported. The people who need the data (damn you, QCA Ofqual) want it as a string of text.

(There is a bigger problem, which is that the database I'm working with is configured so appalingly badly that we can't export data from it in the format we and QCA Ofqual need, so we're having to use Excel as an intermediary.)

F**k, I hate this job. Anybody want a decent educational adminstrator? No? Thought not