ShipStation Date Mismatch!

[DonnaRisso]

I am hoping that someone is able to help with this - I have extracted data from some software called Shipstation and the dates are so messed up!
Firstly they are in US and I need them to be in UK
but if you look at the screenshot, you will see the dates come through in multiple formats in just one report - I have tried my best but I cannot find a way to sort this out - any geniuses out thee who can help?!!

 

[Cubist]

I didn't look at the version but can replace it with LEFT and FIND. I'm not sure about the regional settings.

Book1
	A	B	C
1
2	text format no letters	8/7/2023 12:42:00 PM	8/7/23
3	text format w letters	8/7/2023 9:24:39 AM	8/7/23
4	actual date/time value	8/7/23	8/7/23
Sheet5

Cell Formulas
Range	Formula
C2:C4	C2	=IF(ISNUMBER(B2),B2,DATEVALUE(LEFT(B2,FIND(" ",B2)-1)))

 

[awoohaw]

I didn't look at the version but can replace it with LEFT and FIND. I'm not sure about the regional settings.

Book1
	A	B	C
1
2	text format no letters	8/7/2023 12:42:00 PM	8/7/23
3	text format w letters	8/7/2023 9:24:39 AM	8/7/23
4	actual date/time value	8/7/23	8/7/23
Sheet5

Cell Formulas
Range	Formula
C2:C4	C2	=IF(ISNUMBER(B2),B2,DATEVALUE(LEFT(B2,FIND(" ",B2)-1)))

you made that formula so easy. I always do a rube goldberg contraption!

 

[Alex Blakenburg]

i'm not sure OP has TEXTBEFORE() function. It would be easier to make the formulas if they did though.

The OPs profile says 2019 so it that it the case they won't have TextBefore. For me not having Let or TextSplit is a bigger loss in this scenario.

Typically what happens is that anything with a number <= 12 in what is in the local format the month position gets converted to a date (number) but with the day and month reversed. Anything with a number > 12 will be kept as text.
I think @Cubist Post # 31 is just an example of find instead of textbefore since the formula does not address the US to UK conversion.

20240515 Data US to UK Conversion DonnaRisso.xlsx
	A	B	C	D
1	Date1	Date2	Converted Date1	Converted Date2
2	8/11/2023 12:42	8/22/2023 12:00:00 AM	11/08/2023	22/08/2023
3	8/11/2023 00:41	8/17/2023 12:00:00 AM	11/08/2023	17/08/2023
4	8/11/2023 00:35	8/29/2023 12:00:00 AM	11/08/2023	29/08/2023
5	8/14/2023 9:24:39 AM	9/05/2023 00:00	14/08/2023	5/09/2023
6	8/14/2023 9:20:54 AM	9/05/2023 00:00	14/08/2023	5/09/2023
7	8/14/2023 9:14:43 AM	8/14/2023 12:00:AM	14/08/2023	14/08/2023
8	8/14/2023 9:12:27 AM	8/23/2023 12:00:00 AM	14/08/2023	23/08/2023
9	8/09/2023 12:42	8/22/2023 12:00:00 AM	9/08/2023	22/08/2023
Formula

Cell Formulas
Range	Formula
C2:D9	C2	=IF(ISNUMBER(A2), DATEVALUE(TEXT(A2,"mm/dd/yyyy")), DATE( MID(A2,FIND(" ",A2)-4,4), LEFT(A2,FIND("/",A2)-1), SUBSTITUTE(MID(A2,FIND("/",A2)+1,2),"/","")) )

 

[Cubist]

@Alex Blakenburg Once it's in proper date type in post#31, why can't you just format the cells as UK date?

 

[Alex Blakenburg]

Revised Edited:
@Cubist his regional setting is already UK and I think you will find that the Column formatting is already either General or the Regional setting in this case dd/mm/yyyy.
The dates are coming in as US format mm/dd/yyyy, in this format if mm > 12 then treating that string as if it where dd/mm/yyyy won't work and Excel opts to treat it as text. The 2nd part of the formula in Post #31 using DateValue on that string will error out.
If in the US format mm <=12 then Excel will treat that same string "as if" it where dd/mm/yyyy and will treat it as a number / date. However the month and day are in reverse.

 

[Cubist]

Revised Edited:
@Cubist his regional setting is already UK and I think you will find that the Column formatting is already either General or the Regional setting in this case dd/mm/yyyy.
The dates are coming in as US format mm/dd/yyyy, in this format if mm > 12 then treating that string as if it where dd/mm/yyyy won't work and Excel opts to treat it as text. The 2nd part of the formula in Post #31 using DateValue on that string will error out.
If in the US format mm <=12 then Excel will treat that same string "as if" it where dd/mm/yyyy and will treat it as a number / date. However the month and day are in reverse.

Appreciate the explanation. I don't have much experience dealing with regional formatting. That makes sense.

 

[DonnaRisso]

The OPs profile says 2019 so it that it the case they won't have TextBefore. For me not having Let or TextSplit is a bigger loss in this scenario.

Typically what happens is that anything with a number <= 12 in what is in the local format the month position gets converted to a date (number) but with the day and month reversed. Anything with a number > 12 will be kept as text.
I think @Cubist Post # 31 is just an example of find instead of textbefore since the formula does not address the US to UK conversion.

20240515 Data US to UK Conversion DonnaRisso.xlsx
	A	B	C	D
1	Date1	Date2	Converted Date1	Converted Date2
2	8/11/2023 12:42	8/22/2023 12:00:00 AM	11/08/2023	22/08/2023
3	8/11/2023 00:41	8/17/2023 12:00:00 AM	11/08/2023	17/08/2023
4	8/11/2023 00:35	8/29/2023 12:00:00 AM	11/08/2023	29/08/2023
5	8/14/2023 9:24:39 AM	9/05/2023 00:00	14/08/2023	5/09/2023
6	8/14/2023 9:20:54 AM	9/05/2023 00:00	14/08/2023	5/09/2023
7	8/14/2023 9:14:43 AM	8/14/2023 12:00:AM	14/08/2023	14/08/2023
8	8/14/2023 9:12:27 AM	8/23/2023 12:00:00 AM	14/08/2023	23/08/2023
9	8/09/2023 12:42	8/22/2023 12:00:00 AM	9/08/2023	22/08/2023
Formula

Cell Formulas
Range	Formula
C2:D9	C2	=IF(ISNUMBER(A2), DATEVALUE(TEXT(A2,"mm/dd/yyyy")), DATE( MID(A2,FIND(" ",A2)-4,4), LEFT(A2,FIND("/",A2)-1), SUBSTITUTE(MID(A2,FIND("/",A2)+1,2),"/","")) )

THAT WORKED!!!!!

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.

 

[DonnaRisso]

Team work makes the dream work!

Thank you everyone for making my day