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]

welcome to the forum.

Is the data actually text? Do you need the time component?

 

[DonnaRisso]

Thank you!

I know this must be a tough one as I couldn't find the answer here - and it's always here! I don't need the time just the date

They are showing as general and custom

 

[awoohaw]

when you change it to number does it change to a number? If not, it is text.

 

[DonnaRisso]

I changed all 4 to numbers and this is what they look like

 

[awoohaw]

that is strange that the same type of input wouild have two different data formats.

Book1
	A	B	C
1
2	text format no letters	08/11/2023 12:42	2023-08-11
3	text format w letters	8/14/2023 9:24:39 AM	2023-08-14
4	actual date/time value	2023-08-12 10:45	2023-08-12
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=IF(ISNUMBER(B2),INT(B2),IF(ISNUMBER(FIND("M",B2)), DATE(RIGHT(LEFT(B2,FIND(" ",B2)-1),4), LEFT(B2,FIND("/",B2)-1), LEFT(RIGHT(B2,LEN(B2)-FIND("/",B2)), FIND("/",RIGHT(B2,LEN(B2)-FIND("/",B2)))-1 )), DATE(MID(B2,7,4),LEFT(B2,2),MID(B2,4,2))))

 

[DonnaRisso]

thank you for that! One down - three not so happy!

I have put this in and this is the result:

​

​

​

 

[awoohaw]

Can you look at the updated formula in post 6.?

 

[DonnaRisso]

Sorry - completely missed that! Let me try again!

 

[Cubist]

Try Text-to-Column:

1. Select the cells
2. Data tab -> Text to column
3. Delimiter-> Next
4. Uncheck all the box -> Next
5. Date and MDY -> Finish
6. Ctr +1 -> Number -> dd/mm/yyyy