Dates entered as mm/dd/yyyy not holding their formatting in CSV file - how to get around it

The issue is that I have a user who has to upload a CSV file but with dates formatted as mm/dd/yyyy. He amends the date format and saves the file as CSV (has to be CSV as it's a file for uploading)  but when he opens it the dates have reverted to their original format..how to get the dates to stay in the  mm/dd/yyyy format..
LVL 1
agwalshAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
CSV does not keep any formatting, it is just [Comma Separated] Values. Dates in Excel are just serial numbers (based on a number of days since 01 Jan 1900) so without the formatting will be stored just as a number.

To store the dates as dates in the CSV you will have to convert them to text. Hopefully the receiving program will still recognise them as dates rather than as text.
Rob HensonFinance AnalystCommented:
In a spare column in your file use the following formula to convert to text:

=TEXT(A2,"mm/dd/yyyy")   where date is in A2, copy down as far as required then copy and paste values into the date column.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ste5anSenior DeveloperCommented:
Excel and CSV don't do well, due to the "intelligence"  of Excel. As Rob already wrote, CSV contains only data, whereas Excel files also contains formats. These data and format in Excel are indeed separate things.

To store data from Excel as CSV, Rob already pointed out, that you need to ensure formats by converting/formatting date values explicitly and string them as strings.

in addition to that: Also check your use-case again. Cause mm/dd/yyyy is an ambiguous format. Use e.g. ISO 8601 yyyy-mm-dd or yyyymmdd.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

AlanConsultantCommented:
Hi,

If you have no choice, then I suggest you upload the dates to the CSV as a text string:  "MM/DD/YYY", rather than as a real date or an Excel date.

However, I agree with Ste5an above - this is a ludicrous format for a date.  It is going to cause a lot of confusion and grief.  For example, you would upload 1 Dec 2017 as "12/01/2017" leading most people to think you mean 12 Jan 2017.

I strongly recommend you use the ISO 8601 format as Ste5an suggests, where the most significant figures are on the left (leading), falling to the least significant on the right.  It also means that if you ever need to include a time, it works easily so that, for example 2:15pm on 1 Dec 2017 would be:

201712011415

Or if you want 'formatting' in the sense of delimiters:

2017/12/01/1415

If you cannot use that for some reason, fire the programmer for being an idiot, then use a more normal format such as (sticking with 1 Dec 2017):

01/12/2017

At least that way most people will correctly interpret what you mean even if the format is not ideal in that it reads 'backwards' - it is certainly the most common.

If you go with ISO 8601, it also has the benefit that dates sort alpha-numerically in date order.


Hope that helps,

Alan.
Rob HensonFinance AnalystCommented:
mm/dd/yyyy format may seem odd to us in the UK but it is standard format over the pond and in some places further East.

Assuming the system into which the CSV is being uploaded knows (is programmed) that the date format is mm/dd/yyyy then there should be no issues with it uploading.
AlanConsultantCommented:
Okay, but in a modern world, I would always suggest using a standard approach, not something provincial, plus all the other benefits of using a format where dates naturally sort numerically and chronologically the same are significant in the longer run.

However, as pointed out above, you can use anything you like by making the entry in the CSV into a string.


Alan.
agwalshAuthor Commented:
The dates have to be entered US style - because that's how the system is set up.  I used Rob Henson's text approach, saved as CSV and the newly copied and pasted formatted dates retained their formatting. The company wants ALL its dates in all its branches uploaded in this style.  I've passed this suggestion onto the user and he hasn't come back to me yet so I'm going to close the question. I'm sure if it doesn't work, I'll hear about it :-)
agwalshAuthor Commented:
Got a workaround from the issue from the brilliant experts here...thanks as always.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.