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

 

[DonnaRisso]

i added a 3rd statement in the formula in post 6. I don't understand your worksheet. the dates were in one column earlier.

YOU ARE ABSOLUTELY MARVELLOUS!
I cannot tell you quite how happy this has made me

Thank you so, so much!
Let me know if there is a charity you support and I will ping them some pennies as a thank you!

 

[awoohaw]

YOU ARE ABSOLUTELY MARVELLOUS!
I cannot tell you quite how happy this has made me

Thank you so, so much!
Let me know if there is a charity you support and I will ping them some pennies as a thank you!

You're welcome. I'm pleased you found a solution here.

 

[DonnaRisso]

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?

 

[awoohaw]

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

I think you just need to format your entire column in the format you want. The DATE() function inside the formula I gave just calculates the serial number of the date. (But if the Date() varies from regional setting to regional setting, then you'll need to switch the last two arguments of the date() function.)

 

[DonnaRisso]

I tried that with no joy even checking that it's UK format

 

[awoohaw]

hum. for the dates that are not formatted correctly. In another cell do the DAY() and MONTH() functions on the values. try to determine if at least the value of the date is correct. And confirm which of the 3 possible scenarios affects the result (do a test with the same date, 1 with the AMPM string format, one with the string in 24 Hr Military Format, and one as a date value).

And It just occured to me... the AMPM values will calculate your PM values incorrectly. I need to work on that, definitely. Nevermind, you said you didn't need to worry about time.

 

[DonnaRisso]

I've tried all three formulas:

All with the same result! With the month and day, it is saying it's the 8th of July but it should be 7th of August

 

[awoohaw]

I've tried all three formulas:
View attachment 111392
All with the same result! With the month and day, it is saying it's the 8th of July but it should be 7th of August

what is the proper way to enter the arguments in the DATE() function in England Excel? I input it as DATE(Y,M,D) do you input as DATE(Y,D,M)... and I'll look and see if maybe I have the arguments in the wrong order in the formulas.

 

[awoohaw]

this is with mmm dd, yyyy format:
(the argument sequence I had (have) seem to be correct for my version.)

Also, I know that this may sound strange. But, how about formatting a blank column as a non standard format (mmm dd, yyyy, or yyyy-mm-dd... anything except a standard US or British version). Or even as a regular number format. and then one in british and another in US. And instead of direct pasting into a cell use the F2 key to enter a cell into EDIT mode, and then paste the formula(s). Trying to force excel to think of the format before anything calculations occur.

Book1
	A	B	C
1
2	text format no letters	08/07/2023 12:42	Aug 07, 2023
3	text format w letters	8/7/2023 9:24:39 AM	Aug 07, 2023
4	actual date/time value	2023-08-07 10:45	Aug 07, 2023
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]

I've tried all of that - it's so weird as the one format is perfect!
I've even exported the data again to see if I had corrupted something and then gone into a specific order to check that the order was on the date I thought and all of that is correct

This is a true brain tickler!