changing date formats

[Bernie R.]

I have dates in a column with multiple date formats; ie, 10/08/07, 2-Feb-07,
etc...

The 10/08/07 type dates won't change by changing the format in the "format
cell" option. When I link certain rows to another worksheet, the cells that
contain this "10/08/07" format are not counted in my sum for that column.

Appreciate any assistance.

 

[David Biddulph]

It sounds as if the 10/08/07 cells probably aren't dates, but are text.
Check with =istext(a2) and =isnumber(a2). You might be able to change them
with Data/Text to Colums, or with copying a zero and using Paste Special/
Add on the cells that you want to convert. If the rogue cells are
particularly stubborn, have a look for spaces or other non-printing
charaters before and/or after your date.

 

[Bernie R.]

You're correct, the space(s) are at the end of the data. Is there a "mass"
way of correcting this without deleting this in every cell?

 

[Meebers]

Try the "trim" function?

You're correct, the space(s) are at the end of the data. Is there a
"mass"
way of correcting this without deleting this in every cell?

 

[Bernie R.]

Read the Help section on this and still confused on how to apply.

 

[Pete_UK]

If your data is in A2, the the formula:

=TRIM(A2)

in a helper cell will remove any leading and trailing spaces from the
data. However, it will not remove non-printable characters, or the non-
breaking space character (code 160), which occurs quite often if data
is pasted from a website or HTML document.

In your case if you have no leading spaces and your dates are always
in the form of dd/mm/yy plus extra characters, you can obtain a date
by putting this formula in a helper cell:

=DATE(MID(A2,7,2)+2000,MID(A2,4,2),LEFT(A2,2))

Format the cell as a date and then copy the formula down the column
for as many entries as you have in column A.

Hope this helps.

Pete