0

I have dates stored as text in the following format:

Mar 10, 2015

I want to convert it to a value stored as a date and display it formatted as yyyy-mm-dd:

2015-03-10

I initially tried to use the text to columns feature and parsed all the date components to separate cells. The idea was to then concatenate the cells with formatting that Excel would recognize as a date, such as =B1&"/"&A1&"/"&C1 to produce 10/03/2015. However, Mar requires translation to a month number.

For that matter, I could just concatenate as =C1&"-"&A1&"-"&B1 to get the desired appearance, but Excel treats the results of these string manipulations as text rather than a date.

How do I convert the original text to a date so I can use the Excel number format to display it as desired?

|improve this question
  • Is each component stored in a separate cell? If so, just concatenate exactly what you want. If it is stored in a single cell as a date, just format it the way you want. Can you be more specific about what is stored and what the specific problem is (what you start with, exactly what you do to it, and what the result is)? – fixer1234 Mar 14 '15 at 23:57
  • Em ... the question is pretty self explanatory ... Mar 10, 2015 is not a date format (according to excel) and I want it in a date format (according to excel) so I can sort and organise – Aasim Azam Mar 15 '15 at 0:00
  • Your made-up example, which you claimed you tried, would not have given you the result you posted. You did not explain that it was stored as text, or that converting Mar was an issue. It took someone wasting their time on an answer for you to clarify what the problem was. I've attempted to clarify your question. If it is incorrect, please adjust as necessary. – fixer1234 Mar 15 '15 at 1:48
2

In Excel 2007 English (United States)

Mar 10, 2015

is accepted as a date type and will convert it to

10-Mar-15

Based on your comments, the issue appears to be that the date is stored as text (General). In that case, the following should work (assumes the date is stored in A1):

=DATEVALUE(A1)

This will convert the text date to the numerical form stored internally, which you can then format.

You can change the format to "yyyy-mm-dd" by navigating to the Format Cells:Number menu (Home Tab | Number | Lower Right corner or alt, H, FM), clicking on the custom category, and under Type, type in "yyyy-mm-dd"

This approach leave the cell value as a date, which you can then sort or use for other date functions.

|improve this answer
  • If you type in Mar 10, 2015, Excel recognizes it as a date. It turns out the issue here is that it is pre-existing text pasted from another source. The problem is getting Excel to recognize it as a date or convert it to one. – fixer1234 Mar 15 '15 at 2:42
0

The date-function should do what you want, if you have separated the different parts of the date into individual cells, you could do something like 

=DATE(A3,A2,A1)

judging from how you constructed the date in initial post. Then you can format to look however you want using the cell-format settings.

If you want to use a formula to format the date, you can use the text-function, though you need to make sure that the date is in a format which Excel is able to interpret as a date - I am not sure if this is the case for the values in your original question?

To get the date in your format you ask for, you'd enter something like

=TEXT(<date-value>,"yyyy-mm-dd")
|improve this answer
  • I've not tried this, but the date text was pasted from another source and the cell is formatted as a general cell by defaut – Aasim Azam Mar 15 '15 at 0:06
  • Ah, so your main problem is to get Excel to recognize e.g. "Mar" as a month? – eirikdaude Mar 15 '15 at 0:08
  • That is right, I'm sure I'd need to use an IF function for each of the 12 months – Aasim Azam Mar 15 '15 at 0:09
  • I'd do it manually but there are about 2000 cells to change ... – Aasim Azam Mar 15 '15 at 0:11
  • 1
    to get the month number from the month text you could use MATCH(A1,{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0) – Siphor Mar 15 '15 at 1:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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