Search code examples
exceldatedatetimeexcel-formulaexcel-2011

Problems with converting text to date (YYYY-MM-DD) in Excel (Mac)

I have been able to convert this date to YYYY-MM-DD HH:mm but not anymore. What can I do to convert this date.

Sep 15, 2014 9:30:32 AM

You need to know that I'm using Swedish keyboard, date and region.

Example:

Order # Purchased On 100026881 Sep 15, 2014 9:30:32 AM 100026880 Sep 15, 2014 9:10:56 AM 100026879 Sep 15, 2014 9:09:10 AM 100026878 Sep 15, 2014 9:03:27 AM 100026877 Sep 15, 2014 8:57:02 AM 100026876 Sep 15, 2014 8:38:37 AM 100026875 Sep 15, 2014 6:54:29 AM 100026874 Sep 15, 2014 5:03:23 AM 100026873 Sep 15, 2014 2:45:50 AM 100026872 Sep 15, 2014 1:42:26 AM 100026871 Sep 14, 2014 11:20:31 PM 100026870 Sep 14, 2014 11:16:29 PM 100026869 Sep 14, 2014 11:11:15 PM 100026868 Sep 14, 2014 11:10:06 PM 100026867 Sep 14, 2014 10:42:56 PM 100026866 Sep 14, 2014 10:41:22 PM 100026865 Sep 14, 2014 10:36:43 PM 100026863 Sep 14, 2014 10:26:13 PM

Solution

  • This should be a comment since I have neither Swedish settings not a Mac but I am suggesting a lookup table: 

    +-----+----+
| Jan |  1 |
| Feb |  2 |
| Mar |  3 |
| Apr |  4 |
| May |  5 |
| Jun |  6 |
| Jul |  7 |
| Aug |  8 |
| Sep |  9 |
| Oct | 10 |
| Nov | 11 |
| Dec | 12 |
+-----+----+

    say named Marray, along with: 

     =TEXT(DATE(MID(B2,9,4),VLOOKUP(LEFT(B2,3),Marray,2,0),MID(B2,5,2))+VALUE(TRIM(RIGHT(B2,11))),"[$-41D]mmmm dd, yyyy h:mm:ss AM/PM")

    in C2 and copied down to suit (assuming Sep 15, 2014 9:30:32 AM is in B2).

    For single digit dates, perhaps:

    =TEXT(DATE(TRIM(MID(B2,8,5)),VLOOKUP(LEFT(B2,3),Marray,2,0),SUBSTITUTE(TRIM(MID(B2,4,3)),",",""))+VALUE(TRIM(RIGHT(B2,11))),"[$-41D]mmmm dd, yyyy h:mm:ss AM/PM")

    For me (Windows, Excel 2013, English!) this returns:

    SO25849520 example

    It may be necessary to replace all ,s with ;, except one inside SUBSTITUTE.