converting date format

[zebra7860]

Hi,

I have half a spreadsheet with the date format in the mm/dd/yyyy, followed by the other half with the format I need: YYYY.mm.dd

I tried converting it but couldn't do it.

ANy ideas?

Thank you!

 

[Michael M]

Have you highlighted the cells and changed the format using
Rclick>>format>> Custom>>change the 'type' as "yyy.mm.dd"

or by formula

=TEXT(A1,"yyy.mm.dd")

 

[zebra7860]

Have you highlighted the cells and changed the format using
Rclick>>format>> Custom>>change the 'type' as "yyy.mm.dd"

or by formula

=TEXT(A1,"yyy.mm.dd")

Neither works.

I also tried yyyy.mm.dd........perhaps that was an error in your answer?

Thanks anyway

 

[zebra7860]

Keep in mind that this data was in a CSV format and I copied it into a spreadsheet. Perhaps that has
something to do with the fact that it cant be edited?

 

[Michael M]

Yeah sorry, typo....
Maybe VBA
Highlight the range to be changed then run

Sub MM1()
For Each cell In Selection
    cell.Value = Format(cell.Value, "yyyy.mm.dd")
Next cell
End Sub

 

[zebra7860]

Yeah sorry, typo....
Maybe VBA
Highlight the range to be changed then run

Sub MM1()
For Each cell In Selection
    cell.Value = Format(cell.Value, "yyyy.mm.dd")
Next cell
End Sub

Brilliant!!! Thank you

 

[Michael M]

glad it worked and thanks for the feedback...