CODEWITHSUNDEEP
excelcsvformattingseparator
Lajos Arpad
Lajos Arpad

Reputation: 77030

CSV date and separator issue

I have a CSV file, delimited by comma and I have some date values inside it, like "01/13/2010 05:00:00 AM", or "04/01/2010 05:00:00 AM". The issue is that when I open the CSV file with Microsoft Excel, it will show these days as 01/13/2010 05:00:00 AM and 04/01/2010 05:00, respectively. The value of the second one is 04/01/2010 05:00:00, but it still shows 04/01/2010 05:00. I've made a few tests and I've realized that Excel is not able to determine which is the day and which is the month in a few cases. In fact Excel gets a string like:

__/__/____ __:__:__ __

It deduces from the last two characters (AM/PM) the value of hour, it knows what the minute and second is, also, it determines the year when it sees the four-character long number sequence, so the question is about month and day. If the first and the second number from the string are both <= 12, then it can't determine which is the month and which is the year, so it messes up my date format.

I would like to have a date format such as

MM/dd/yyyy HH:mm:ss AM/PM

and I've found out that using the function called TEXT is able to do just that, like this:

=TEXT("04/13/2010 05:54:00 AM", "MM/dd/yyyy HH:mm:ss AM/PM")

It works like a charm, but it still guesses which is the day and which is the month. However, assuming that the clients will have the same default date format as the data in the CSV, the month and the day won't be inverted. However, I don't want to open Excel and type the formula in, because I need to give this file to clients and it should show the right value whenever they open the file. So, I need to write the formula inside the comma-separated file, but... TEXT is two-dimensional and there is a comma separating my date from my format.

If I type:

= ABS(-5),=TEXT("04/13/2010 05:54:00 AM", "MM/dd/yyyy HH:mm:ss AM/PM")

into my file, I will see 3 cells, the first being 5, the second is =TEXT("04/13/2010 05:54:00 AM" and the third is "MM/dd/yyyy HH:mm:ss AM/PM").

How can I prevent this behavior? Can I use something else instead of the comma inside my function call? Or can I change the separator? Or is there a better solution?

Can you enlighten me, to learn how can I enter function calls inside a CSV file?

Thanks in advance to everybody who tries to help,

Lajos Árpád.

Upvotes: 2

Views: 2111

Answers (1)

Reinier Torenbeek
Reinier Torenbeek

Reputation: 17383

If I type:

= ABS(-5),=TEXT("04/13/2010 05:54:00 AM", "MM/dd/yyyy HH:mm:ss AM/PM")

into my file, I will see 3 cells, the first being 5, the second is =TEXT("04/13/2010 05:54:00 AM" and the third is "MM/dd/yyyy HH:mm:ss AM/PM").

To make this example work, you need to surround your second data item with double-quotes to escape the comma. As a consequence, you need to use double double-quotes to have them interpreted as double-quotes inside the double-quotes -- I hope this is a readable sentence.

So the example you give should work if you use this in your file:

=ABS(-5),"=TEXT(""04/13/2010 05:54:00 AM"", ""MM/dd/yyyy HH:mm:ss AM/PM"")"

Upvotes: 2

Related Questions