Re-formatting data to use DATE-characteristics?

This is my first post, so you can clearly see that I am an Excel novice. I received over 1000 rrithdates in a spreadsheet, coded as dd.mm.yyyy, e.g. "20.10.1956". Unfortunately, this is one format excel does not recognize as a date. So it does not assign a number as it usually does. As a result, my simple formula (=(TODAY()-A2)/365.25) only produces an error.
I have tried re-formatting the data to dates, but it still only shows the original entry, not the number when I go on general.
Is there any way to get excel to understand that this is a date, or do I have to key them in again?

Hi dune2,

Your date appears to be formatted to text, so if your date is in A1, try this in B1

=ABS(A1), then format to date

oldchippy

Thanks Chippie, but this only produced further errors. I think the dots are throwing of this function. I agree that the data is probably formatted as text. But why can't I change it? Anything else I can try?

Hi dune2,

Sorry about that, Neik's first formula works for me though

oldchippy

=DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2))
or, if 1.10-1956 occurs also:
=DATE(RIGHT(A2,4),MID(A2,IF(LEN(A2)=9,3,4),2),LEFT(A2,IF(LEN(A2)=9,1,2)))
I hope you don't have 10.1.1956 but if you do, post again in this thread

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"dune2" <dune2.2c829q_1155041706.2647@excelforum-nospam.com> wrote in message
news:dune2.2c829q_1155041706.2647@excelforum-nospam.com...
|
| This is my first post, so you can clearly see that I am an Excel novice.
| I received over 1000 rrithdates in a spreadsheet, coded as dd.mm.yyyy,
| e.g. "20.10.1956". Unfortunately, this is one format excel does not
| recognize as a date. So it does not assign a number as it usually does.
| As a result, my simple formula (=(TODAY()-A2)/365.25) only produces an
| error.
| I have tried re-formatting the data to dates, but it still only shows
| the original entry, not the number when I go on general.
| Is there any way to get excel to understand that this is a date, or do
| I have to key them in again?
|
|
| --
| dune2
| ------------------------------------------------------------------------
| dune2's Profile: http://www.excelforum.com/member.php...o&userid=37228
| View this thread: http://www.excelforum.com/showthread...hreadid=569392
|

Hi

Select your column, then go to Data/Text to columns. On the third page (I
think) in the top-right corner is an option to tell Excel that the cells are
dates. Finish it off and the job's done. Make a backup before you start.

Hope this helps.
Andy.

"dune2" <dune2.2c829q_1155041706.2647@excelforum-nospam.com> wrote in
message news:dune2.2c829q_1155041706.2647@excelforum-nospam.com...
>
> This is my first post, so you can clearly see that I am an Excel novice.
> I received over 1000 rrithdates in a spreadsheet, coded as dd.mm.yyyy,
> e.g. "20.10.1956". Unfortunately, this is one format excel does not
> recognize as a date. So it does not assign a number as it usually does.
> As a result, my simple formula (=(TODAY()-A2)/365.25) only produces an
> error.
> I have tried re-formatting the data to dates, but it still only shows
> the original entry, not the number when I go on general.
> Is there any way to get excel to understand that this is a date, or do
> I have to key them in again?
>
>
> --
> dune2
> ------------------------------------------------------------------------
> dune2's Profile:
> http://www.excelforum.com/member.php...o&userid=37228
> View this thread: http://www.excelforum.com/showthread...hreadid=569392
>

@Niek Otten: THANKS!!!

That formula worked like a charm! Good stuff!