Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
    
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 Nov 15 '12 at 19:35
    
@Majkl Is the missing space in the date typo? Or is it the actual value? –  Alexey Ivanov Nov 16 '12 at 19:12
add comment

3 Answers

up vote 1 down vote accepted

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.

share|improve this answer
1  
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 –  barry houdini Nov 16 '12 at 0:07
add comment

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.

share|improve this answer
    
Re-entering might not exactly be the preferred method for those 100,000 entries the OP mentioned. ;-) –  TheUser1024 Nov 15 '12 at 20:12
2  
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. –  Alexey Ivanov Nov 15 '12 at 20:20
    
@AlexeyIvanov Good point. That is assuming the missing space is just a typo in the OPs post. –  TheUser1024 Nov 15 '12 at 20:24
    
@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. –  Isaac Rabinovitch Nov 15 '12 at 21:58
add comment

It is very simple:

  1. Select all dates you want to change.
  2. Do a Find/Replace (Ctrl+F)
  3. Find . and replace with / (if that is what you want or vice versa)
  4. After that, select all, go to Format and select the date format you need
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.