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

01.06.2014 =DATEVALUE(AF88) 01.05.2000 =DATEVALUE(AF89) 32994 #Value! 07.11.2011 01.04.2005 <colgroup><col width="186" style="width:140pt" span="2"> </colgroup><tbody> </tbody>

<colgroup><col width="186" style="width:140pt" span="2"> </colgroup><tbody>
</tbody>

 

[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