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.
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.