uploading Excel date format data from a csv file

[liamsheridan]

G'day, I am trying to upload data from a csv file, one of the fields is a date field in the following format - 2/7/2007 0:00:00 - in the source data this means 2nd July 2007. Despite using various upload options in the wizard (eg DMY) when the data is uploaded although it "looks" the same analysis using the MONTH() function indicates that this has been uploaded in a way which has coverted the date to 7th February 2007. I'm in NZ where we tend to use the British date convention of day month year. Can anyone suggest a way of retaining the 2nd July convention in the example above?
Looking forward to hearing from you.
Regards

 

[Ralajer]

Welcome to the board.

I am not sure that this can be done specifically for Excel but the only way I could get the date to come in the dd/mm/yyyy format is to change the Region and Language setting in the control panel.

Depending on what the settings used on installation your region should be set to English (New Zealand) but since you are having this issue it may not be. You will have to check and/or change your Region and Language settings. They can be found in the control panel or by running intl.cpl from the run box (Windows Key+R). On the Format tab change the format box to English (New Zealand). You will have to restart Excel for the change to take effect.

Let me know if this works for you.

 

[liamsheridan]

Hi, this worked! Sorry for the delay in responding.
Thank you for your assistance.
Cheers
Liam

 

[Ralajer]

Liam,

Thanks for the reply as this is the first time I've had come across this type of issue and I wanted to verify that the solution worked, on a computer other than my own.