format of date after array resort - one date refuses to play ball

[nigelog]

Hi
The following code lifts data from a sheet and via an array resorts copies it back the same sheet. The date is in the correct format "11/07/19" before macro is run

Please Login or Register  to view this content.

The dates are returned to column 3 and even though the column is formatted to date and the code specifies the date format our friend above resolutely returns as "07/11/19"

No other dates seem affected

Any ideas

[AliGW]

Is the date in question really a date, or text?

[sintek]

Try this ...

Please Login or Register  to view this content.

[nigelog]

@ Ali .. yes export data comes in as a date

@ sintek .. no go...

Ill upload the workbook. Macro is stalled after array sort

copy sheet1 to use export data again as deleted by macro

[sintek]

The date is in the correct format "11/07/19" before macro is run

what range is this troublesome date

Edit...Also, when you make use of With statement always ensure that the point is added to reflect correct

Please Login or Register  to view this content.

[nigelog]

Hi Sintek

date is highlighted in yellow "E7" sheet(1)

[sintek]

Tricky little bugger...
I just re-coded a snippet...this works...

Please Login or Register  to view this content.

Incorporate into your code...

[nigelog]

Hi Sintek

was just giving up on it when you posted

Good shot ....that nailed him

[sintek]

...........................................
Thanks.gif

[rorya]

FWIW, you should also be able to simply amend the original code to read:

Please Login or Register  to view this content.

[nigelog]

I forgot all about its use Rory.. would have saved so much time. Thanks for the pointer Rory

[sintek]

Same here lol...