Date format

[Michael007]

Hi all,

how could I change date format from 2018-10-14 18:40:47 to 14.10.2018?

I tryied format cells to date but nothing happened.

Please help.

Thanks!

[#2]

Format Cells\Custom. Enter dd.mm.yyyy in the text box.

[#3]

@JMB17 

Thank you for your response. I changed the date format manually but the date is still in old format. I have to double-click to cell and press enter and now it is OK. I have about 9000 rows and I can´t click and enter one by one. How could I change all of them by one click?

Thank you.

[#4]

Hello,

To make the Date Format permanent:
1. Type in Control Panel on your system
2. Under Clock and Region, click on Change date, time or number formats
3. Click on Additional Settings
4. Select Date tab
5. In the Date Formats section, clear what you have there and type in dd.mm.yyyy.
5. Click OK and OK.

When you return to Excel, all the date with yyyy-mm-dd will turn to dd.mm.yyyyy.


Do let me know how it pans out after following the steps

[#5]

It sounds like your data is actually text. It doesn't appear background error checking will pick up dates that are formatted as text, so I'll suggest using text to columns.

First, make sure you have a backup copy of your file before trying something new.

Click data tab, text to columns, to open the wizard. Select delimited, next, uncheck all of the delimiters, next, select date and YMD (dropdown), finish.

You'll likely have to apply your custom format again. Also, note that the custom format only changes how it appears on the screen, the underlying cell value will still retain the time value (the decimal part of the number).