Formatting date/times issue

[jamiek]

Hi,

I am trying to sort orders by date/time within the CSV export from my order system, however when I try to sort it does the dates correctly, but not the times (hour/minute).

How can I make it so the sort works for the hour/minute as well? I tried changing the formatting to see if that would help but for some reason no matter what formatting I select, the formatting doesn't actually change the text displayed in the cells.

Please see image below.

Link to CSV file on Google Drive here link to CSV

Would love some help

Thanks

 

[JamesCanale]

The data isn't recognized as a date/time. It's just text to excel.

You might highlight all of the data in question, go to the "Data" tab, select "Text to Columns" and then finish.

 

[Joe4]

It looks like the fields are formatted as Text, and not as valid Date/Time fields, so it will not sort the way that you want (it will follow alphabetic sorting rules, not numeric ones).
If you do a "Text to Columns" on column A, choosing the Date option with the "MDY" format, and clicking Finish, it should then convert all those values to valid date/time values that you can sort.
If you want to change the display format of the field after doing that, you can do that in order to get it to look the way you want.

 

[jamiek]

The data isn't recognized as a date/time. It's just text to excel.

You might highlight all of the data in question, go to the "Data" tab, select "Text to Columns" and then finish.

It looks like the fields are formatted as Text, and not as valid Date/Time fields, so it will not sort the way that you want (it will follow alphabetic sorting rules, not numeric ones).
If you do a "Text to Columns" on column A, choosing the Date option with the "MDY" format, and clicking Finish, it should then convert all those values to valid date/time values that you can sort.
If you want to change the display format of the field after doing that, you can do that in order to get it to look the way you want.

Thanks both.

I just tried this and it formats the first 3 rows of dates but not the others, even though they are selected.

What could be preventing Excel from formatting the rest of the dates?

 

[Joe4]

Regional date setting, perhaps.
If you are not using American-standard dates (MDY) and your default settings are (DMY), it thinks the first part is a day, and not a month, and the second part is a month, and not a day, which is problematic when you get to values over 12.

Are you using a European version of Excel, or do you know what your default date settings are?

 

[jamiek]

Regional date setting, perhaps.
If you are not using American-standard dates (MDY) and your default settings are (DMY), it thinks the first part is a day, and not a month, and the second part is a month, and not a day, which is problematic when you get to values over 12.

Are you using a European version of Excel, or do you know what your default date settings are?

Yes, I am in the UK so my standard is (DMY) but my order system export uses (MDY).

Do I need to change something in the preferences of Excel then?

 

[Joe4]

Hmmm... This is hard for me to test, because I am in the United States, so cannot really replicate your situation easily.
You could change your Windows Regional Setting, but I don't know if you really want to do that.

Another option is to use formulas to convert the entries in to valid dates.

 

[Dave Patton]

1. Insert 2 columns B and C.
2. Select the range such as A2:A6 and use Data Text To Columns with Delimiter " " space
Date specify MDY
Do not import columns Skip for Time and for PM
Put the data into B2
3. Enter the formula into C2 and fill down
4. Convert C2:C6 to values
5. Delete columns B and C
6. Format to your preference

USA dates to International.xlsm
	A	B	C
1
2	12/27/2020 11:09:04 PM	27-Dec-20	27-Dec-2020 23:09
3	2/4/2020 11:09:04 PM	4-Feb-20	04-Feb-2020 23:09
4	11/12/2021 11:09:04 PM	12-Nov-21	12-Nov-2021 23:09
5	11/12/2021 4:09:04 AM	12-Nov-21	12-Nov-2021 04:09
6	4/27/2021 6:00:00 PM	27-Apr-21	27-Apr-2021 18:00
7
1e

Cell Formulas
Range	Formula
C2:C6	C2	=B2+MID(A2,FIND(" ",A2)+1,11)

 

[Dave Patton]

Instead of inserting columns, you can put the date from Text to Columns after the data. See below.
If you prefer, convert column D to values and then copy to column A
Then delete columns C and D.
Could your order system export in another format say yyyymmdd hh:mm:ss?

USA dates to International.xlsm
	A	B	C	D
1	Date - Order Date	Order - Number	Date	Date and Time
2	11/12/2021 12:22:29 AM	2247724330	12-11-21	12-Nov-21 00:22:29
3	11/12/2021 4:28:50 PM	2248015150	12-11-21	12-Nov-21 16:28:50
4	11/12/2021 9:40:50 PM	2246922433	12-11-21	12-Nov-21 21:40:50
5	11/13/2021 10:18:13 PM	2250119962	13-11-21	13-Nov-21 22:18:13
6	11/13/2021 3:16:01 PM	2247780675	13-11-21	13-Nov-21 15:16:01
7	11/13/2021 5:41:50 AM	2249024638	13-11-21	13-Nov-21 05:41:50
8	11/13/2021 8:03:40 PM	2248195649	13-11-21	13-Nov-21 20:03:40
9	11/13/2021 8:20:05 PM	2249916634	13-11-21	13-Nov-21 20:20:05
10	11/13/2021 9:42:52 PM	2250058574	13-11-21	13-Nov-21 21:42:52
11	11/14/2021 4:54:16 AM	2250620042	14-11-21	14-Nov-21 04:54:16
12	11/14/2021 8:16:09 PM	2249745437	14-11-21	14-Nov-21 20:16:09
13	11/14/2021 8:55:09 PM	2251673718	14-11-21	14-Nov-21 20:55:09
14	11/15/2021 1:41:04 PM	2252826170	15-11-21	15-Nov-21 13:41:04
15	11/15/2021 10:53:41 PM	2253717554	15-11-21	15-Nov-21 22:53:41
16	11/15/2021 10:57:14 AM	2250616739	15-11-21	15-Nov-21 10:57:14
17	11/15/2021 11:04:15 PM	2251526195	15-11-21	15-Nov-21 23:04:15
18	11/15/2021 11:09:36 AM	2250622491	15-11-21	15-Nov-21 11:09:36
19
1d

Cell Formulas
Range	Formula
D2:D18	D2	=C2+MID(A2,FIND(" ",A2)+1,11)

 

[Dave Patton]

A VBA solution see the results below.
select the relevant range such as A2:A18
run the macro It converts the text to Values and Formats with the system defaults
The sub also custom formatted the date and time

Sub FixDatesMDY()
  
  With Selection
    .TextToColumns DataType:=xlDelimited, Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 5)
    .NumberFormat = "dd-mmm-yyyy hh:mm:ss"
  End With
End Sub

USA dates to International.xlsm
	A	B
1	Date - Order Date	Order - Number
2	12-Nov-2021 00:22:29	2247724330
3	12-Nov-2021 16:28:50	2248015150
4	12-Nov-2021 21:40:50	2246922433
5	13-Nov-2021 05:41:50	2249024638
6	13-Nov-2021 15:16:01	2247780675
7	13-Nov-2021 20:03:40	2248195649
8	13-Nov-2021 20:20:05	2249916634
9	13-Nov-2021 21:42:52	2250058574
10	13-Nov-2021 22:18:13	2250119962
11	14-Nov-2021 04:54:16	2250620042
12	14-Nov-2021 20:16:09	2249745437
13	14-Nov-2021 20:55:09	2251673718
14	15-Nov-2021 10:57:14	2250616739
15	15-Nov-2021 11:09:36	2250622491
16	15-Nov-2021 13:41:04	2252826170
17	15-Nov-2021 22:53:41	2253717554
18	15-Nov-2021 23:04:15	2251526195
19
3a