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?!!

 

[awoohaw]

i've seen this reported in other threads. Maybe you can search for Date Issues with Regional Settings and formats?

 

[Cubist]

The second column is great - the first column is still showing the date in US format:

08/07/2023 11:23​

8/21/2023 12:00:00 AM

08/07/2023​

Is there a way to switch them?

View attachment 111390

Which method is this reference to?

 

[awoohaw]

Which method is this reference to?

@Cubist, i'm not sure you mean by method? This is a confusing dilemma. I saw 3 different ways the dates were presented. And, for me sometimes the xl2bb changes the format when pasting. I am in US, she has US formatted dates (often as strings); but wants them in UK format. When the formula I provided is calculated it forces formatting around and gives an incorrect datevalue than what she expected. I am baffled.

 

[Cubist]

@Cubist, what do you mean by method? I saw 3 different ways the dates were presented. And, for me sometimes the xl2bb changes the format when pasting. I am in US, she has US formatted dates (often as strings). When the formula I provided is calculated it forces formatting around and gives an incorrect datevalue than what she expected. I am baffled.

I was wondering if the OP tried the text-to-column or your formula in that post. It appears that s/he was referring to the formula now that I re-read it.

 

[awoohaw]

I was wondering if the OP tried the text-to-column or your formula in that post. It appears that s/he was referring to the formula now that I re-read it.

Yeah. I think OP probably ruled out using TextToColumn because of the various formats. I've seen these types of formatting in date conversions conflicts before. I've not seen how they get resolved. Do you think if the formula created a text string in UK regional format and then wrap that in DATEVALUE() would work?

 

[Cubist]

I think the format is pretty consistent. I don't see an issue with my sample. The results are showing in the US, but can be easily changed to UK format.

Book2
	A	B	C
1	Text	Formula	Text-to-Column
2	08/11/2023 12:42	8/11/2023	8/11/2023 12:42
3	08/16/2023 12:42 PM	8/16/2023	8/16/2023 12:42
Sheet4

Cell Formulas
Range	Formula
B2:B3	B2	=IF(ISNUMBER(A1),A1,DATEVALUE(TEXTBEFORE(A2," ")))

 

[awoohaw]

I think the format is pretty consistent. I don't see an issue with my sample. The results are showing in the US, but can be easily changed to UK format.

Book2
	A	B	C
1	Text	Formula	Text-to-Column
2	08/11/2023 12:42	8/11/2023	8/11/2023 12:42
3	08/16/2023 12:42 PM	8/16/2023	8/16/2023 12:42
Sheet4

Cell Formulas
Range	Formula
B2:B3	B2	=IF(ISNUMBER(A1),A1,DATEVALUE(TEXTBEFORE(A2," ")))

that is the problem. When she formats as UK the month and day get reversed. Aug 7 become Jul 8.

 

[Cubist]

I did Ctrl + 1 and changed to d/m/yyyy

Book2
	A	B	C
1	Text	Formula	Text-to-Column
2	08/11/2023 12:42	11/8/2023	11/8/2023
3	08/16/2023 12:42 PM	16/8/2023	16/8/2023
Sheet4

Cell Formulas
Range	Formula
B2:B3	B2	=IF(ISNUMBER(A1),A1,DATEVALUE(TEXTBEFORE(A2," ")))

 

[awoohaw]

@DonnaRisso ... if you are still getting the reversed day and month try this hair brained idea:

the following formula gives me a #VALUE error because I'm in the US. But the DATEVALUE() function takes a date that is in a text string and converts it to the serial number. So, the first 3 cells in column C are in UK format, and I get a #Value error. I'm not sure if you will or not. But, try to get the "text" order of your dates into a format that the DATEVALUE() function likes. The bottom two cells are text that work into the DATEVALUE function for me.

Book1
	A	B	C	D
1
2	text format no letters	08/07/2023 12:42	07/08/2023	#VALUE!
3	text format w letters	8/7/2023 9:24:39 AM	7/8/2023	#VALUE!
4	actual date/time value	2023-08-07 10:45	07/08/2023	#VALUE!
5			2023/8/7	45145
6			August 7, 2023	45145
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=IF(ISNUMBER($B2),TEXT(INT($B2),"dd/mm/yyyy"), IF(ISNUMBER(FIND("M",$B2)), LEFT(RIGHT($B2,LEN($B2)-FIND("/",$B2)),FIND("/",RIGHT($B2,LEN($B2)-FIND("/",$B2)))-1) &"/"&LEFT($B2,FIND("/",$B2)-1) &"/"& RIGHT(LEFT($B2,FIND(" ",$B2)-1),4), MID($B2,4,2)&"/"&LEFT($B2,2)&"/"&MID($B2,7,4)))
D2:D6	D2	=DATEVALUE(C2)

 

[awoohaw]

I did Ctrl + 1 and changed to d/m/yyyy

Book2
	A	B	C
1	Text	Formula	Text-to-Column
2	08/11/2023 12:42	11/8/2023	11/8/2023
3	08/16/2023 12:42 PM	16/8/2023	16/8/2023
Sheet4

Cell Formulas
Range	Formula
B2:B3	B2	=IF(ISNUMBER(A1),A1,DATEVALUE(TEXTBEFORE(A2," ")))

i'm not sure OP has TEXTBEFORE() function. It would be easier to make the formulas if they did though.
But, I really think it is the regional settting on the OP computer that is causing the mix up. I've seen it before in posts here, but never seen a satisfactory resolution.