Date Format Issue

[Ritzl]

Dear all,
I have a stupid but very strange thing. I entered dates in an excel sheet (formated all as date). When I look at the formula (Show Formula) it shows me the date once as "Date" and in other lines as "Number". Any idea how I can convert this? It gives me an error message when I want to make further operations. I copy pasted at an other place and back, changed format to text, back to date....nothing happens....Any Iea? Thanks in advance for any help Rgds Marc
[TABLE="width: 372"]
<colgroup><col width="186" style="width:140pt" span="2"> </colgroup><tbody>[TR]
[TD="width: 186, bgcolor: #FFCC99"][TABLE="width: 372"]
<colgroup><col width="186" style="width:140pt" span="2"> </colgroup><tbody>[TR]
[TD="width: 186, bgcolor: #FFCC99"]01.06.2014[/TD]
[TD="width: 186, bgcolor: #FFCC99"]=DATEVALUE(AF88)[/TD]
[/TR]
[TR]
[TD="bgcolor: #FFCC99"]01.05.2000[/TD]
[TD="bgcolor: #FFCC99"]=DATEVALUE(AF89)[/TD]
[/TR]
[TR]
[TD="bgcolor: #FFCC99"]32994[/TD]
[TD="bgcolor: #FFCC99"]#Value![/TD]
[/TR]
[TR]
[TD="bgcolor: #FFCC99"]07.11.2011[/TD]
[TD="bgcolor: #FFCC99"][/TD]
[/TR]
[TR]
[TD="bgcolor: #FFCC99"]01.04.2005[/TD]
[TD="bgcolor: #FFCC99"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="width: 186, bgcolor: #FFCC99"][/TD]
[/TR]
[TR]
[TD="bgcolor: #FFCC99"][/TD]
[TD="bgcolor: #FFCC99"][/TD]
[/TR]
[TR]
[TD="bgcolor: #FFCC99"][/TD]
[TD="bgcolor: #FFCC99"][/TD]
[/TR]
[TR]
[TD="bgcolor: #FFCC99"][/TD]
[TD="bgcolor: #FFCC99"][/TD]
[/TR]
[TR]
[TD="bgcolor: #FFCC99"][/TD]
[TD="bgcolor: #FFCC99"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Special-K99]

This will turn values like 01.04.2004 to European date format 01/04/2004
All Excel dates, e.g. 32994, will remain the same.

With dates in column A

in B1
=IF(ISNUMBER(SEARCH(".",A1)),SUBSTITUTE(A1,".","/")+0,A1)
and copy down the column

Copy column B
Paste Values into column A overwriting the original data
Delete column B

The values in column A should now all be dates

 

[Ritzl]

Excellent. Thx a lot!

 

[Ritzl]

Ohh, just realized I need it the other way around. When I take the formula --> show formulas view, all dates should be shown as a number like 32994. Now I have also dates like 7.11.2011 but I can not show them as number. E.g also the filters do not recognise those as dates even if optically it is correctly displayed as a date. If I overwrite eg. a date shown and formated in the date format (7.1.2011) then it is shown as number. But I have hundreds of dates an can not overwrite them all manually. Never seen such a thing.....

 

[Special-K99]

Lets get this straight so I dont waste too much time on what should be a simple task.

So your dates

01.06.2014 you want as 06/01/2014 ?

Then you need this:
=IF(LEN(A1)<>5,(MID(A1,4,2)&"/"&LEFT(A1,2)&"/"&RIGHT(A1,4))+0,A1)
and copy down the column
then format them in whatever date format you want