changing date format

[burnsa9]

Hello, I am trying to change the date formate. right now it looks like 01.01.1111 I want it to look like 1/1/1111. I tried uisng the number formating, but it wont work. when I select an example the formula bar has an apostrophe in front of the date so I don't know if that has something to do with it. an example: '01/01/1111

 

[Scott T]

Your date is a text string so number formatting does not change it. Remove the apostrophe and then it will be an Excel date that you can use formatting on.

 

[Fluff]

The apostrophe means that the values are text, you'll need to use text to columns conversion
Highlight the column then On the Data tab select Text to Columns & click Finish

 

[burnsa9]

Turns out it was a simple Ctrl + F comand and replace the "." with "/"

 

[MARZIOTULLIO]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

a​

[/TD]
[TD]

b​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD] 01.01.1111[/TD]
[TD] 01/01/1111[/TD]
[/TR]
</tbody>[/TABLE]


b1=SUBSTITUTE(A1,".","/")

remenber Excel stores dates in sequential serial numbers beginning with January 1st, 1900 so 01/1/1111 is a text not a date