Excel Changing Date Formats on its own

[hmltnangel]

Hi folks, I do hope you can help

Cell T38 in my worksheet has a formula as follows:

{=TEXT(MIN(IF('Amendments Data'!N:N="Future Tasks",'Amendments Data'!H:H)),"dd/mm/yyyy")}

It correctly calculates the date I need.

As part of some VBA a new sheet is created, this cell is copied, then pasted 'Values only' into the same cell on another sheet.

If the date is eg. 15/11/2018 then it correctly inputs the date in the new sheet.

However if the date is 01-12/11/2018 then it changes the date to show 11/01-12/2018.

I cant fo rthe life of me figure out why. Anyone got an idea?

[TMS]

Well, 01-12/11/2018 is NOT a date so it doesn't really matter how you format it.

[hmltnangel]

I was making the point that any number between 01-12 it changes the format, any number over that - it doesn't.

[TMS]

Ah, ok. Then that's because VBA treats dates with the American Format of month day year. And, of course, a day 12 or less can be confused with a month.

[hmltnangel]

Thanks, so is it just a simple addition of specific format to the way VBA pastes it in then?

[TMS]

Maybe get and save the date value rather than the date text. Then paste the date value and post as required.