Excel Date Time Format Changes Automatically

[Shumai]

Hi can anyone help me with my problem,

I'm trying to insert a value from another wb to another wb using macro. The source date is 1/7/2021 but when the macro insert the value in another wb the date changes to 7/1/2021. How to prevent the cell value to change its date format?

The data type for the date in the source file is "Short Date"

Thanks and regards,
Shumai

 

[severynm]

Welcome!

I am assuming that "7/1/2021" is your locale default date format. You can use Range.NumberFormat to set the format of your dates. You could call it either as you enter the value for each date, or once you are finished on the entire range of dates, either should be ok. In the latter case, the code would look like

SheetName.Range("A2:A18").NumberFormat = "m/d/yyyy"

, or whichever format you need.

 

[Shumai]

Welcome!

I am assuming that "7/1/2021" is your locale default date format. You can use Range.NumberFormat to set the format of your dates. You could call it either as you enter the value for each date, or once you are finished on the entire range of dates, either should be ok. In the latter case, the code would look like

SheetName.Range("A2:A18").NumberFormat = "m/d/yyyy"

, or whichever format you need.

Thank you sir! I have solved the problem. The system date time format was set to US format. Changed to UK everything paste accordingly.