Date format changing in csv file

[Deanne]

We have .csv files that are automatically created and then loaded into a
billing system. Currently the users need to go into these .csv files to
analyse the data and make any necessary changes before the file is loaded
into the billing system.

When the file is created by the original system the date column is in the
format DD/MM/YYYY HH:MM and this is the format that the billing system
expects to receive it. However, once the user opens the file and then saves
it the date format chagnes to MM/DD/YYYY HH:MM. I've double checked by
opening the file in notepad once it has been saved and the date format is
definitely being changed.

I have checked the regional settings under control panel and confirmed that
all dates are set to MM/DD/YYYY. This issue has caused some major issues in
our billing system as the dates no longer correspond to data already in the
system.

Please help.

 

[Tyro]

How does Excel enter into this? You don't state anything about Excel

Tyro

 

[Fred Smith]

Why not change the Regional Settings to dd/mm/yyyy?

Regards,
Fred.

 

[Deanne]

When the users analyse and update the data they use excel to open the file.

 

[Deanne]

Sorry I should have put that the regional settings are dd/mm/yyyy not
mm/dd/yyyy.

 

[Tyro]

Are you aware that Excel maintains dates as numbers? Excel's dates range is
Jan. 1, 1900, day 1 through Dec. 31. 9999, day 2,958,465. March 18, 2008 is
day
39,525. When you format that number as mm/dd/yy you see 03/18/08 which is
for human consumption. But the cell contains a simple number. Are your dates
Excel's dates, i.e. numbers, or are they text, that is to say, not dates but
the human presentation? In other words, the cell contains "March 18, 2008"
or "03/18/08" as opposed to the number 39,525? To verify press Ctrl + Accent
grave (the key above the tab key). If the cell that appears as a date is an
Excel date, it will show as a number. If text, it won't. Press Ctrl + Accent
grave to return to normal display.

Tyro

 

[David Biddulph]

If the users don't want Excel to change the format of the csv file, the
shouldn't use File/ Open or double-click on the csv file, but should open it
with Data/ Import External Data and specify the columns as Text.

 

[Deanne]

Hi David,

I've tried your suggested option and it appears to work. Many thanks for
that.

We have only recently come across this issue as users used to be able to
open these csv files in excel, edit, save and they would stay in the same
date format (ie dd/mm/yyyy). This problem appears to be occurring on users
with new PCs. Any ideas on what may be done (or not done) during an excel
install which would cause this problem?

Thanks again

Deanne

 

[David Biddulph]

If it's not a difference of Windows Regional Options, I'm not sure what it's
likely to be.