1

For my work, I regularly open data in Excel 365 that comes to me in a weird CSV format that I can't control. Here's an example:

"Call-off","Receiver ext.","Plant","Status","Supplier ext.","UnPt","Line station","Part family","Production No","Sequence no.","Series/Model","Call-Off date","Trig.type","EDI Arrival","No. old","No. new","SubDel.","Receiver ID","Trailer","File-No.","Created","Date Creation","Last change","Timestamp last Update"
"2238575","=""8010""","=""T21""","=""95""","=""18519280E""","=""*""","=""*"""," ","=""0006459842""","=""20191119T039""","=""16716112""","11/20/2019 12:04 AM","=""IB""","11/20/2019 12:00 AM","6443","6444","=""0""","=""MBUSI"""," ","43889667","=""AMSSTIN""","11/20/2019 12:07:54 AM","=""SSTIFILE""","11/20/2019 12:07:54 AM"
"2238574","=""8010""","=""T21""","=""90""","=""18519280E""","=""*""","=""*"""," ","=""0006459842""","=""20191119T039""","=""16716112""","11/20/2019 12:04 AM","=""IB""","11/20/2019 12:00 AM","6442","6443","=""0""","=""MBUSI"""," ","43889666","=""AMSSTIN""","11/20/2019 12:07:54 AM","=""SSTIFILE""","11/20/2019 12:07:54 AM"
"2238572","=""510898""","=""456""","=""90""","=""33265005""","=""56""","=""BROADCAST"""," ","=""0006459842""","=""300000162834""","=""167""","11/20/2019 12:04 AM","=""SMP""","11/20/2019 12:00 AM","56386","56387","=""0""","=""SMP"""," ","43889664","=""AMSSTIN""","11/20/2019 12:07:53 AM","=""SSTIFILE""","11/20/2019 12:07:53 AM"

An example Excel file is here.

I need to add formulas that operate with the dates and times in the data (see the last column in the example data). The trouble is, my Excel sees the dates as strings. This leads me to insert a table then do Data -> From table/range which opens up a new sheet with the fake formulas removed leaving only values (and destroying numbers with leading zeroes). My other alternative is to insert a helper column and parse the date out of the date string.

The thing is, whenever my colleagues do the same as I do, following the instructions I've written, they get their dates as proper dates. This is operating on the same data. And weirder, I've had this issue on two separate machines, but I've never seen my colleagues experience it.

This leads me to believe that there must be some setting somewhere in Excel that's changing things for me. How can I make my Excel work like my colleagues'?

NOTE: I'm not asking how to work around this problem as I already know how to do that. I'm asking why it's happening in the first place and/or how I can prevent it from happening without resorting to workarounds.

Improve this question
8
  • Make sure the column in which the dates are being placed is not formatted as text but general before importing the data.
    – Scott Craner
    Nov 22, 2019 at 15:41
  • The formatting is all handled by Excel before I even have a chance to do anything. The data app I use downloads a CSV file then opens it in Excel. However it opens is however it opens. Nothing I can do unless there's some setting I can change.
    – Scott Severance
    Nov 22, 2019 at 15:45
  • 2
    I cannot reproduce your problem with your CSV data. Could it be that your Windows Regional Settings are DMY and your colleagues as MDY? If that is the case, the dates in your example would be interpreted as text (and dates that are NOT interpreted as text would be incorrect.
    – Ron Rosenfeld
    Nov 22, 2019 at 22:08
  • @RonRosenfeld: Yes, it turns out that any time I want to import data from CSV, I have to set my Windows date format to the format of the CSV file--and I routinely deal with data from a variety of countries using a variety of date formats. Could Excel have come up with a worse way of handling this?
    – Scott Severance
    Nov 26, 2019 at 16:06
  • If you import, you can avoid that issue, and all your dates will be handled correctly.
    – Ron Rosenfeld
    Nov 26, 2019 at 19:28

2 Answers 2

Reset to default
-2

(Downvoters: Aren't you ashamed?)

To convert a column to format Date:

  • Select the column (hover over the header and click when the down-arrow appears)
  • In the Home tab, click Format > Format Cells ...
  • In the dialog, tab Number, select "Date" and set "Locale" to "English (United States)"
  • Click OK.

The column should now be of type Date.

If you wish to verify that, use in an empty cell the formula =VALUE(AS2) and drag-extend it over the entire column.

What I have discovered:

The Excel documentation is misleading, if not wrong. The date format of the cell apparently only serves for display. Excel will parse the strings using operating system functions, so it is crucial to have the right date format in Windows.

In Control Panel > Region, tab Formats, I have set "Format:" to "English (United States)". Without this setting, Excel wouldn't accept American-type dates.

I have selected the entire column, pressed Ctrl+1 to enter the dialog of "Format Cells", tab Number, and set the format to "Custom", with the value of [$-en-US]m/d/yyyy h:mm AM/PM.

That was enough for the column AU that I added with the function of =VALUE(AS2) to now correctly analyze the AS column as numeric, giving a date-time numerical value:

enter image description here

Another idiosyncrasy of Excel is that, although now numeric, the cells stay left-justified. If one double-clicks inside one cell, then clicks out, the cell will suddenly display as right-justified. Go figure.

Improve this answer
8
  • This doesn't work and it doesn't answer the question. Formatting a column as a date doesn't coerce strings to dates; it only formats numbers. My dates are being seen as strings. Secondly, I can already manually address the issue. My question is why I'm having this problem while nobody else is.
    – Scott Severance
    Nov 22, 2019 at 18:58
  • My good fellow: Downvoting an answer that works for everyone but not for you isn't really the best tactic. A better move would have been to post an example of your spreadsheet file, and with the proof that it doesn't work, because it must absolutely work unless something very funny is involved. Downvoting doesn't really motivate a person to help.
    – harrymc
    Nov 22, 2019 at 19:03
  • My apologies for the downvote; I voted in haste. Unfortunately my vote is now locked in. I posted an example above. I explained that on my machine dates were being seen as strings (when I hit "Evaluate formula" I see double quotes around my dates and errors popping up when I try to do calculations with them). Of course, formatting cells only applies number formatting to numbers, not to strings (is it "text" in Excel speak?). In my example, you can see that the original CSV has dates in double quotes, so one might argue that what I'm seeing is correct. But others don't have the same issue.
    – Scott Severance
    Nov 22, 2019 at 19:49
  • The vote is no longer locked-in (I edited the answer trivially). Also, before answering, I tried to recreate your data and it worked for me. There is something funny in the data you are receiving, which might not show up when posting here. That's why I thought that a real example file is required.
    – harrymc
    Nov 22, 2019 at 20:05
  • I've removed my downvote. I don't know who the other downvoter is. I've added a link to an Excel file. Since you've tried to recreate my issue and came up with nothing, welcome to my world! My problem seems unique to me.
    – Scott Severance
    Nov 22, 2019 at 21:16
0

Use this in AU2 to convert AS2 to a date;

=DATEVALUE(MID(AS2,4,2)&"/"&MID(AS2,1,2)&"/"&MID(AS2,7,4))

It happens because the date is in a non-US format. The source data needs to be changed.

Improve this answer
5
  • As I stated in my question, I'm not looking to workaround the problem; I already know how to do that. I'm looking for why it happens only to me. harrymc has provided the answer.
    – Scott Severance
    Nov 26, 2019 at 16:02
  • @ScottSeverance I have stated why it happens as well and what you need to do.
    – Naz
    Nov 26, 2019 at 19:48
  • Except it's incorrect. The date is in the US format. My machine was configured to use ISO format. And as I stated above, the source data is out of my control; I can't change it.
    – Scott Severance
    Nov 26, 2019 at 19:56
  • @ScottSeverance How are you accessing the file through desktop Excel or through Excel 365 online via OneDrive or Sharepoint?
    – Naz
    Nov 26, 2019 at 20:04
  • Through desktop Excel.
    – Scott Severance
    Nov 26, 2019 at 20:16

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .