Change format of cell and re-input data

[1Roberta1]

Hi
I was wondering if someone could point me in the direction for answering my problem. I have searched the net and this site but unfortunately have not managed to come up with a possible solution.

Background:
I have inherited a spreadsheet that holds Date of Births and Ages. The person who created and was completing the spreadsheet would manually calculate the age and I noticed that some were wrong so I thought I had nothing better to do than to use =ROUNDDOWN((TODAY()-A2)/365.25,0) which I know works as I have used it in the past.

My problem:
Having tried to use the above formula and getting #VALUE! error, I took a look at the format of the cells in Column A.
The Date Of Birth shows as 15.04.1975, however the format has not been customised to dd.mm.yyyy, the format has been set to General, I believe because of this Excel is reading the date of births as Text and therefore can not calculate the Age.
If I select the full row and change the format to dd.mm.yyyy it does not alter what is in the cell and does not fix the calculating problem. You can tell that the custom format has not worked as the D.O.B is aligned to the left (as if it were text, not that the left align button has been selected).

I did find a very long way around fixing this by changing the format to custom dd.mm.yyyy but then I have to re-enter the Date Of Birth, this fixes the format problem and my age formula then works.

I have been doing this for several hours now and still have a few thousand rows to go.

Can anyone help me with a quicker solution?

I know that I could probably just get on with it, my worry is that I know that there are other workbooks where I will come across the same if not similar scenario, so any advice would really be appreciated.

I hope the above makes sense.

Thanks in advance for reading this, and further thanks if you can assist me.

 

[schielrn]

Select the cells that are in that format, just select a couple for the first try. Make sure it is only one column.

Then go to data-->text to columns.

Press Next twice.
Then select Date radio button and change the drop down to DMY and click finish and see if that does what you want.

Hope that helps.

 

[xenou]

maybe:

=DATEVALUE(SUBSTITUTE(A1,".","/"))

Also:

Using Text-to-Columns you can break it into 3 columns of day-month-year.

EDIT:

Press Next twice.
Then select Date radio button and change the drop down to DMY and click finish and see if that does what you want.

That's right. I always forget this works right in the same column...This is the best solution I think too.

 

[1Roberta1]

schielrn
ray:

Thank you so much, that is absolutely perfect, so quick and easy too

You have saved me so much time and trouble, thank you again.

and thank you to Alexander Barnes too

 

[schielrn]

Thanks for the feedback and glad I could help. I learned that from someone else a while back and was glad I could pass the knowledge on.