Re-formatting data to use DATE-characteristics?

D

dune2

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? :eek:
 
O

oldchippy

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 :
 
N

Niek Otten

=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

|
| 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? :eek:
|
|
| --
| dune2
| ------------------------------------------------------------------------
| dune2's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=37228
| View this thread: http://www.excelforum.com/showthread.php?threadid=569392
|
 
D

dune2

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

oldchippy

Hi dune2,

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

oldchippy :
 
G

Guest

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