Excel thinking the date is in UK format but its actually in US. no way to convert it

[fapb48]

Hi everyone,

I have an export file with a field of "call time" that contains date that is in US format mm/dd/yyyy

But for whatever reason excel thinks this is UK date format and no way to convert this field to a proper date?

Screenshots below:

Converting text to columns and select DMY will not make any changes at all

 

[Fluff]

i have now tried with legacy import and select the MDY option but same old. Nothing changes.

In that case can you supply the csv?

 

[fapb48]

In that case can you supply the csv?

T202301170006_0103_AN0mJqlTSSardqi5eXur (1).xlsx

Microsoft Excel Workbook

1drv.ms

 

[Fluff]

Thanks for that. When you open that file are all the dates in UK format including the ones on Jan 31st?

 

[fapb48]

Thanks for that. When you open that file are all the dates in UK format including the ones on Jan 31st?

all dates on the sheet are in US format (MDY)

 

[awoohaw]

i have tried this formula but it errors? does it not need the year, month and day functions?

technically, what it needs are the year, month, and day arguments....which are what those 3 calculations provide.

 

[Dave Patton]

I used Data Text to Columns with MDY

Select the range of text such as A2:A8
Data Data Text to Columns Next Next select Date MDY unselect time and AM and then Finish

Custom format the dates to the format that you prefer.
Do you want to retain the time?

Book1
	A
1	Date
2	2023-Jan-31
3	2023-Jan-31
4	2023-Jan-31
5	2023-Jan-31
6	2023-Jan-31
7	2023-Jan-31
8	2023-Jan-31
Sheet1

 

[Dave Patton]

Book1
	A	B
14	Call Time
15	1/31/2023 7:14:07 AM	07:14:07
16	1/31/2023 8:09:56 AM	08:09:56
17	1/31/2023 8:11:34 AM	08:11:34
18	1/31/2023 8:12:39 AM	08:12:39
19	1/31/2023 8:14:06 AM	08:14:06
20	1/31/2023 8:16:39 AM	08:16:39
21	1/31/2023 8:16:11 AM	08:16:11
22
Sheet1

Book1
	A	B	C
14	Call Time
15	1/31/2023 7:14:07 AM	07:14:07
16	1/31/2023 8:09:56 AM	08:09:56
17	1/31/2023 8:11:34 AM	08:11:34
18	1/31/2023 8:12:39 AM	08:12:39
19	1/31/2023 8:14:06 AM	08:14:06
20	1/31/2023 8:16:39 AM	08:16:39
21	1/31/2023 8:16:11 AM	08:16:11
22
23	2023-Jan-31	07:14:07	2023-Jan-31 07:14
24	2023-Jan-31	08:09:56	2023-Jan-31 08:09
25	2023-Jan-31	08:11:34	2023-Jan-31 08:11
26	2023-Jan-31	08:12:39	2023-Jan-31 08:12
27	2023-Jan-31	08:14:06	2023-Jan-31 08:14
28	2023-Jan-31	08:16:39	2023-Jan-31 08:16
29	2023-Jan-31	08:16:11	2023-Jan-31 08:16
30
Sheet1

Cell Formulas
Range	Formula
B15	B15	=--TEXTAFTER(A15," ")
C23:C29	C23	=A23+B23

 

[Dave Patton]

Formula

Book1
	A	B	C
14	Call Time
15	1/31/2023 7:14:07 AM	07:14:07	2023-Jan-31 07:14
16	1/31/2023 8:09:56 AM	08:09:56
Sheet1

Cell Formulas
Range	Formula
B15	B15	=--TEXTAFTER(A15," ")
C15	C15	=DATE(TEXTBEFORE(TEXTAFTER(A15,"/",2)," "),TEXTBEFORE(A15,"/"),TEXTBEFORE(TEXTAFTER(A15,"/"),"/"))+B15

 

[fapb48]

Formula

Book1
	A	B	C
14	Call Time
15	1/31/2023 7:14:07 AM	07:14:07	2023-Jan-31 07:14
16	1/31/2023 8:09:56 AM	08:09:56
Sheet1

Cell Formulas
Range	Formula
B15	B15	=--TEXTAFTER(A15," ")
C15	C15	=DATE(TEXTBEFORE(TEXTAFTER(A15,"/",2)," "),TEXTBEFORE(A15,"/"),TEXTBEFORE(TEXTAFTER(A15,"/"),"/"))+B15

Life Saver!!!! Thank you so so much!!