Opening a csv file with us date format on a australian pc solved


Opening a csv file with US date format on a Australian PC
"Troy Lea" <[email protected]> wrote in message 
news:[email protected]. 
> Having problems with formating dates correctly in Excel when opened from a 
> CSV file. The data coming in is in the US format "MM/DD/YYYY HH:MM AM/PM". 
> IE: "6/17/1985 12:00:00 AM". 
> 
> When the CSV file is opened in Excel you are unable to change the 
> formatting 
> to set it to Australian date format "DD/MM/YYYY HH:MM AM/PM"; the data 
> stays 
> the same as "6/17/1985 12:00:00 AM". 
> 
> When the MM and DD digits are 12 and less it recognises them as valid 
> dates. 
> IE "11/1/1995 12:00:00 AM" is automatically formated as "11/01/1995 0:00". 
> However this is only happening because both MM and DD are equal to or less 
> than 12. 
> 
> My question is how do I import the data into Excel telling it that the 
> format of the data coming in is "MM/DD/YYYY HH:MM AM/PM" and I want it 
> formatted as "DD/MM/YYYY HH:MM AM/PM"? Unfortunately when we receive the 
> data 
> from the database it includes the time in the date field, we have no 
> control 
> over this. 
> 
>
Opening a csv file with US date format on a Australian PC 
Opening a csv file with US date format on a Australian PC
Having problems with formating dates correctly in Excel when opened from a 
CSV file. The data coming in is in the US format "MM/DD/YYYY HH:MM AM/PM". 
IE: "6/17/1985 12:00:00 AM".
When the CSV file is opened in Excel you are unable to change the formatting 
to set it to Australian date format "DD/MM/YYYY HH:MM AM/PM"; the data stays 
the same as "6/17/1985 12:00:00 AM".
When the MM and DD digits are 12 and less it recognises them as valid dates. 
IE "11/1/1995 12:00:00 AM" is automatically formated as "11/01/1995 0:00". 
However this is only happening because both MM and DD are equal to or less 
than 12.
My question is how do I import the data into Excel telling it that the 
format of the data coming in is "MM/DD/YYYY HH:MM AM/PM" and I want it 
formatted as "DD/MM/YYYY HH:MM AM/PM"? Unfortunately when we receive the data 
from the database it includes the time in the date field, we have no control 
over this.
03-16-2005, 07:06 AM #2
Re: Opening a csv file with US date format on a Australian PC
if you change the file extension before importing to .txt you will then get 
the import wizard when you open the file in Excel. one of the screens 
allows you to specify the date format of the imported data - you might like 
to give this a go and see if it helps
Cheers 
JulieD 
Perth, Western Australia
"Troy Lea" <[email protected]> wrote in message 
news:[email protected]. 
> Having problems with formating dates correctly in Excel when opened from a 
> CSV file. The data coming in is in the US format "MM/DD/YYYY HH:MM AM/PM". 
> IE: "6/17/1985 12:00:00 AM". 
> 
> When the CSV file is opened in Excel you are unable to change the 
> formatting 
> to set it to Australian date format "DD/MM/YYYY HH:MM AM/PM"; the data 
> stays 
> the same as "6/17/1985 12:00:00 AM". 
> 
> When the MM and DD digits are 12 and less it recognises them as valid 
> dates. 
> IE "11/1/1995 12:00:00 AM" is automatically formated as "11/01/1995 0:00". 
> However this is only happening because both MM and DD are equal to or less 
> than 12. 
> 
> My question is how do I import the data into Excel telling it that the 
> format of the data coming in is "MM/DD/YYYY HH:MM AM/PM" and I want it 
> formatted as "DD/MM/YYYY HH:MM AM/PM"? Unfortunately when we receive the 
> data 
> from the database it includes the time in the date field, we have no 
> control 
> over this. 
> 
>
03-16-2005, 07:06 PM #3
Re: Opening a csv file with US date format on a Australian PC
We've tried that but unfortunately because there is time included in the 
field this method doesn't work.
"JulieD" wrote: 
 
> Hi Troy 
> 
> if you change the file extension before importing to .txt you will then get 
> the import wizard when you open the file in Excel. one of the screens 
> allows you to specify the date format of the imported data - you might like 
> to give this a go and see if it helps 
> 
> Cheers 
> JulieD 
> Perth, Western Australia 
> 
> "Troy Lea" <[email protected]> wrote in message 
> news:[email protected]. 
> > Having problems with formating dates correctly in Excel when opened from a 
> > CSV file. The data coming in is in the US format "MM/DD/YYYY HH:MM AM/PM". 
> > IE: "6/17/1985 12:00:00 AM". 
> > 
> > When the CSV file is opened in Excel you are unable to change the 
> > formatting 
> > to set it to Australian date format "DD/MM/YYYY HH:MM AM/PM"; the data 
> > stays 
> > the same as "6/17/1985 12:00:00 AM". 
> > 
> > When the MM and DD digits are 12 and less it recognises them as valid 
> > dates. 
> > IE "11/1/1995 12:00:00 AM" is automatically formated as "11/01/1995 0:00". 
> > However this is only happening because both MM and DD are equal to or less 
> > than 12. 
> > 
> > My question is how do I import the data into Excel telling it that the 
> > format of the data coming in is "MM/DD/YYYY HH:MM AM/PM" and I want it 
> > formatted as "DD/MM/YYYY HH:MM AM/PM"? Unfortunately when we receive the 
> > data 
> > from the database it includes the time in the date field, we have no 
> > control 
> > over this. 
> > 
> > 
> 
> 
> 

03-16-2005, 08:13 PM #4
Forum Contributor Join Date 03-13-2005 Posts 6,195
Dates
Troy, I think the answer to your question is that you don't. 
If I am correct dates are held in some Julian format and displayed as per the format of the cell, so, allow the import to pick-up the date, then set a helper column to copy the date and display it in a new format. 
On importing dates, I believe Excel 97 sets the format for the column from the first item, whereas Excel 2000 onwards allows you to state the format of the column, so you may need to doctor the first item to set the format for '97. 
(note: I would welcome any better explanation)

« Previous Thread | Next Thread »
Thread Information
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)