Help Needed on a date conversion

[barron2245]

Hello All,

I am in need of some help. I have a column of dates (dates of birth) that are "yyyy-mm-dd" and I need to convert them to "mm-dd-yyyy" and/or the person's actual age. I know how to convert the second date range to age, but can't figure out how to convert the "yyyy-mm-dd" format to anything useful. Any help with this matter would be greatly appreciated. I am sorry if I am posting this in the wrong area. I am new and running out of time.

[jeffreybrown]

Maybe look at text to columns

Data >> Data Tools >> Text to Columns >> Next >> Next >> Data (pick a format)

[barron2245]

Thank you for the tip. I did try that (after you pointed it out) and for some reason it worked on some of the dates, but not all of them. Any other ideas?

[Winon]

Select the Column or Cells you want to convert to "mm-dd-yyyy", right Click and then Click on Format. Select Custom and enter the format you want.

[jeffreybrown]

Probably because those date really are not dates.

To test them...

=ISNUMBER(A1)

...if it is a real date then you will get a TRUE

[Winon]

To add to jeffreybrown's solution. If it looks like a Date and you get a FALSE result, then check for leading or trailing, or any Spaces for that matter and delete it if you find any.

[barron2245]

I just figured it out.. Thank you for all of your help. I used the text to columns feature, since each space was in same spot I used "fixed width" and inserted a break at the end of the date and it worked!

[barron2245]

YAY! Well, now I know what it was... There is a space after the yyyy/mm/dd date, you can't see it, but I insert the cursor, hit delete, then enter and the date changes itself to mm/dd/yyyy.. Okay, so now to further the question, how can I delete the space in the entire column without deleting them one at a time?
Thank you all for your help and suggestions, they were very much appreciated!!!!!!

[jeffreybrown]

You can also use the Trim function which in some cases may work better.

=========================================================

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

[Winon]

You are welcome.

Glad we could help.

Thank you for the Rep.