• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

region independent date format in cells

bijicha

Member
Hi friends,

I have a macro which v-lookup the date of births from another file. the issue what i am acing is when i am sharing my final file with different region, date format is changing. Is there any way to make the date value column in yyyy-mm-dd, irrespective of regional settings.

Below is the format which i currently have
>>> use code - tags <<<
Code:
    Columns("G:G").Select
    Selection.NumberFormat = "[$-409]yyyy\-mm\-dd;@"
Thanks
 
Last edited by a moderator:

bijicha

Member
Hi Chihiro,

You mean to make the code as below. If so i was used this earlier, but it changed when this macro is running in another region system.



Code:
Selection.NumberFormat = "yyyy\-mm\-dd"
 

Chihiro

Excel Ninja
No need to escape dash with "\" in this case.

? How is macro used. That formatting will override any other system side setting.
With one exception. Conditional format applied on range will override any custom Number formatting applied.
 

bijicha

Member
Hi Chihiro,

Apology for late reply, was not on system access. Macro is just copying the dates from one csv file, which is open as local. original csv DOB is as dd/mm/yyyy, so i am converting it into yyyy-mm-dd format. when the receiver open on their system, this yyyy-mm-dd format, change and showing as dd/mm/yyyy. so i just want to make this colum, in call cases, irrespective of opening system region format, should show as yyyy-mm-dd.

let me know you need more details..

thanks
 

Chihiro

Excel Ninja
When you are importing from csv. You must specify data type at import stage. Not after.
CSV is just a comma delimited text file and lacks any schema info. Therefore, it is required that you specify what format the stored data is in, then process accordingly.

I.E. using import wizard, you should specify that source is in "DMY" format. The column will then be treated as date value. After import you can apply any formatting you wish. Date value is consistent across all system.
 
Top