The original date is in the following format:
I need it in this form:
When I choose
Number -> Custom -> d.m.yyyy h:mm -> OK , nothing changes.
How do I change it? The number of dates is over 100000.
If you only need to do that once with your 100k entries: make a helper column with this formula (assuming your string is in col A):
This assumes you always have
That worked for me in Excel2007, maybe you have to try around with the Text to Columns function a bit to get exactly what you want.
If you have to do it more than once, i would try to create a macro using something like
If you entered either of the values you give, the cell doesn't contain a date, it contains a string. You can't change as string just by changing the format!
Excel tries to guess your data type, and that can be really confusing. If you enter some kind of data with an obvious type (date, time, date/time, number) it can figure that out. If the type isn't obvious, the it assumes you're entering a string.
If you enter an obvious date/time value like
Re-enter the date-time value correctly, and you'll have a date-time value. (Actually, you'll have a number formatted as a date-time, but let's not nitpick.) Then you can control the way it's displayed.
It is very simple: