0

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

pnuts
  • 58,317
  • 11
  • 87
  • 139
argit
  • 3
  • 1
  • 3

3 Answers3

1

Formatting a date in Excel 2011 for Mac

You have at least three different ways to apply a date format. Perhaps the fastest is to select a cell or cell range, and then click the Home tab of the Ribbon. In the Number group, click the pop-up button under the Number group title and choose Date to display the date as m/d/yy, where m represents the month's number, d represents the day number, and yy represents a two-digit year.

Excel has many more built-in date formats, which you can apply by displaying the Format Cells dialog by pressing Command-1 and then clicking the Number tab. You can also display the Number tab of the Format Cells dialog by clicking the Home tab on the Ribbon. Then click the pop-up button under the Number group title and choose Custom from the pop-up menu.

When the Format Cells dialog displays, select the Date category. Choose a Type from the list. Choosing a different Location (language) or Calendar type changes the date types offered. enter image description here

I hope this may helps..

Gagan Gami
  • 10,121
  • 1
  • 29
  • 55
  • Thats the problem it do not work with select cell and format cell to date. I dont know why. @Gagan – argit Sep 15 '14 at 14:11
0

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.

pnuts
  • 58,317
  • 11
  • 87
  • 139
  • Thanks for your reply @pnuts but what does "$-41D" do? – argit Sep 16 '14 at 12:18
  • 1
    And I have a problem when this code when the date changes the number of characters ex. `100026642 Sep 9, 2014 11:54:55 PM` – argit Sep 16 '14 at 12:25
  • It is very confusing this because my colleague could get this sorted on date before OSX update... @pnuts – argit Sep 16 '14 at 12:39
0

I think that Jeeped might be close to the problem.

My guess is that now the data may have been pasted as text, instead of recognizing the date. (pnuts had an answer but it's a lot more work than using the builtin Excel functions.)

If the dates are in their own column, like:

     Sep 15, 2014 9:30:32 AM
     Sep 15, 2014 9:10:56 AM
     Sep 15, 2014 9:09:10 AM
     etc

Then you might have to get Excel to parse the text dates.

If the date text is in B2, put this formula in another cell (say B3):

     =DATEVALUE(B2) + TIMEVALUE(B2)

Then you can format with this custom formatting string: yyyy-mm-dd h:mm:ss AM/PM

Which will give you:

      2014-09-15 9:30:32 AM
      2014-09-15 9:10:56 AM
      2014-09-15 9:09:10 AM
      etc.

Hope this helps.

dzurn
  • 1
  • 1