In Excel 2010 why can't I change the date format?

Question

The original date is in the following format:

14/Nov/201110:53:44

I need it in this form:

14.11.2011 10:53:44

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.

Solution 1

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):

=LEFT(A1,11) & " " & RIGHT(A1,8)

This assumes you always have DD/MMM/YYYY, in case you don't it gets a bit more complex. Copy the formula down (obviously) and then copy and paste special (values) the result. Then go to the Data Tab and hit "Text to Columns" choose "delimted" and hit finish. Now you should have an actual date you can format in there.

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 CDate(left(...)+" "+right(...)) in a for loop. Too tired to provide precise syntax now though.

Solution 2

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 14/Nov/2011 10:53:44 (notice the space between the date and the time) it understands that you're entering a date. But 14/Nov/201110:53:44 (no space separating date and time) confuses it, causes it to record a string.

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.

Comments

[TheUser1024]

So there is no Space between 2011 and 10:53:44, correct? I think you need a two part approach, first turn your string into something Excel will recognize as a date by adding a whitespace between date and time and then your formatting will work. You might have to do it with a macro though unless you only need to do it once.

[TheUser1024]

Re-entering might not exactly be the preferred method for those 100,000 entries the OP mentioned. ;-)

[Alexey Ivanov]

Excel would not recognize 14/Nov/2011 10:53:44 as date if Regional settings are non-English. This might be the case why Excel does not recognize it as the date for OP.

[TheUser1024]

@AlexeyIvanov Good point. That is assuming the missing space is just a typo in the OPs post.

[Isaac Rabinovitch]

@TheUser1024 Sloppy of me to miss that "little" detail (which you did not). Still, the important part of solving the problem is understanding how dates work in Excel. It seemed obvious that Majkl didn't get that, and that was the source of the problem.

[barry houdini]

to cater for possible single digit days like 4/Nov/2011 you could use this formula to insert the space =REPLACE(A1,FIND("/",A1)+9,0," ")+0. The +0 at the end will convert to numeric so you don't need "text to columns" - just format in required date format