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.

I have a proper custom cell format for my date set as "ddd mm.dd.yyyy h:mm AM/PM".

I'd like to be able to copy/paste from another source a date that is formatted as such and have Excel recognize it as a date instead of text.

But when I paste while keeping destination formatting, it still doesn't recognize it as a date, and I cannot do calculations with it. Pasting as "07/27/2013 9:46 AM" works fine since Excel can tell that it's a date and parses it accordingly... but it doesn't seem to recognize the periods as delimiters like the slash.

Would I need to use some kind of scripting, or is there some tweaking that can be done in the settings?

share|improve this question
    
if you type it in manually and assign it your custom cell format, does it recognize it as date or text? Can you do calculations with it? –  Raystafarian Jul 30 '13 at 20:04
    
what do you mean recognize it as a date? what are you trying to do with the information? when you add it into the custom category it uses all the date markup characters. my answer may not be what you are looking for. I didn't read the entire question before I posted. my bad –  Malachi Jul 30 '13 at 21:56

3 Answers 3

The cell format will only display a numeric value as a date. That does not mean that text that happens to have the same appearance will be interpreted as a date when the text is pasted into the cell.

If your regional settings do not have the dot . in the date format, but rather the slash / then a pasted value will not be recognised as a date. The ddd prefix does not help either.

You may want to edit your source data and replace all . with /.

Then get the data into Excel and perform a Text to columns to remove the first four characters, i.e. the Sat. Use the fixed width setting for that and put in a break line after the first space. Remove any other break lines. Click next and specify that the second column is a date with the correct order of M, D and Y. The result should then be real date values, which can be pasted into your formatted range as values only, to appear formatted as specified.

share|improve this answer

if you take the ddd mm.dd.yyyy h:mm AM/PM and put it into your Format Cell window

enter image description here

and hit enter it will add it to your Custom Category.

I think that might work for you.

Update

When I enter 7/30/2013 into a cell that has that format painted in it already it converts it to Tue 07.30.2013 12:00 AM automatically. and it allows me to Sort from oldest to newest or newest to oldest.

I would say that it is Recognizing it as a Date

Check this Out!

this might be the Answer that you may or may not love.

I surfed the Web and found this, but I cannot change the date format on this machine becuase it is locked on my work machine. but I figured that you could give it a try.

Date With Periods

it is a little ways down the page but this is what it says

If you want the format to be the default for all dates, then you are going to have to make a change in the Regional Settings of Windows itself. (Excel draws its default from these settings.) The exact way you do this varies depending on the version of Windows you are using, but in general there is a choice in the Windows Control Panel that allows you to specify regional settings. All you need to do is modify those settings to reflect the date format you want to use. The change will affect not only the display of numbers in Excel, but in other Windows-compliant programs, as well.

share|improve this answer
    
When I enter 7/30/2013 into a cell that has that format painted in it already it converts it to Tue 07.30.2013 12:00 AM automatically. and it allows me to Sort from oldest to newest. –  Malachi Jul 30 '13 at 22:00
1  
That's not the issue - If you enter 7.30.2013, it does not get recognized as a date. I realize that 7/30/2013 does. Otherwise, I already have everything set up as you did. –  agent154 Jul 30 '13 at 22:05

If you have your date string in cell A1, then put

=DATEVALUE(SUBSTITUTE(MID(A1,5,10), ".", "/")) + TIMEVALUE(RIGHT(A1,8))

into B1, or wherever is convenient.  If you have many such values in Column A, then drag/fill this down into the corresponding cells of Column B.  Then either use the Column B values for all your calculations, or copy and paste values onto Column A.

share|improve this answer

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.