0

I have a column of dates in the following format ddd mmm dd hh m:s "EST" yyyy. In another cell in another sheet, I wish to have the dates in the format dd/m/y. How can I do this?

I already tried the DATEVALUE function. Seeing as the positions are fixed, I started using the RIGHT and MID functions to extract components to put into the DATE function. Unfortunately, I don't know of a way of converting the three letter string to a month without writing a huge if block.

UPDATE: I managed to convert the string using MONTH(1&THREE_LETTER_DATE). I am still curious if there is a better solution though

Improve this question

2 Answers 2

Reset to default
2

It is one of those excel annoyances. The original data - even if it looks like as a Date type - might not be the type of Date but Text.

A recreation of this problem:

  • Create a new sheet
  • select a cell
  • change its data type to Text
  • enter in the same cell: '3343423' (just the numbers), press Enter
  • change the cell's data type back to Number => Nothing happens!
  • select the cell, press F2, press Enter => presto! It's now a Number.

Now back to the original question:

Say, if A1 is the original data (and A2 is empty) first make sure A1's type is set to Data, the format is Custom and the custom format describes exactly the original data. (You can try it the other way around. Try setting the current date in a cell to your custom format until the result is satisfactory).

If A1 seems OK, then set A2 to "=A1", next click back on A1, press F2 (edit) and press Enter.

Improve this answer
2
  • You are right. In Office 2007, a number can be stored as text, in which case it is left instead of right aligned and you see a warning when you click on the cell. Changing the format does nothing until you hit F2. Actually, there is also a box on the home tab that contains the type. It seems that despite my efforts to convert it to a custom format, it is still saved as text
    – Casebash
    Dec 22, 2010 at 3:31
  • If you ever played with older games, it is just fun to press F2-Enter-F2-Enter... a few hundreds time, but for a few thousand lines it could get boring.
    – karatedog
    Dec 22, 2010 at 22:54
0

There's no need to use a formula for this, just use the Cell Format options. (As in, copy the dates as desired -either as values or as references-, and then format the destination cells as dd/m/y.)

Improve this answer
4
  • It doesn't work. Maybe it has something to do with the "EST"
    – Casebash
    Dec 21, 2010 at 0:41
  • 2
    @Casebash: You could try =DATEVALUE(SUBSTITUTE(A1,"""EST""","")) to remove the "EST" from a date in cell A1 before processing it with DATEVALUE.
    – Mike Fitzpatrick
    Dec 21, 2010 at 1:16
  • It works just fine for me. Here's a step-by-step: (1.) On Sheet 1, type in a date and time. (2.) Select the cell(s) with the dates, go to Format - Cells - Number tab, select Custom category, put ddd mmm dd hh m:s "EST" yyyy in the Type box, click OK. (3.) On Sheet 2, type = in a cell, then go back to Sheet 1, select the date, and hit Enter. (4.) Select the cell on Sheet 2, go to Format - Cells - Number tab - Custom - enter dd/m/y in the Type box.
    – Martha
    Dec 21, 2010 at 16:04
  • I think the key might be to copy the dates to the new sheet using simple bare cell references, not DATEVALUE or any other function.
    – Martha
    Dec 21, 2010 at 16:08

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .