changing date formats

  • Thread starter Thread starter Bernie R.
  • Start date Start date
B

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.
 
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.
 
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?
 
Try the "trim" function?

Bernie R. said:
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?
Click to expand...
 
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
 
Back
Top