Excel And Its Infuriating Date Rigmarole

Okay, just a warning…this is about an obscure corner case technical problem (at least it seems that way to me) and its solution. Well, my solution at least.

I use Microsoft Excel at work fairly often. I frequently create formulas which generate INSERT or UPDATE statements for an Oracle database. Well today I just got fed up with an annoying quirk concerning the way Excel handles dates. Here’s the situation:

  • One of the columns on my spreadsheet is formatted as a date like this: 1-May-2008
  • I have a formula to generate an INSERT statement which uses the date in the WHERE clause.
  • I want to paste the result of the formula into a database tool so I can execute the query.
  • When I copy and paste a value directly from the date column, I see the date as I expect it. When I paste the result of the formula, the date shows up in stupid Excel date-time code.
  • I puzzled at this for a while, and flailed about on Google looking for an answer, until a co-worker asked a question which provided the solution: “Is the data a date or a string?” That fired off a thought-process which led to my solution:

    1. Copy and past all the values in the column to a text editor.
    2. Change the format of the column in Excel to “Text” instead of “Date.”
    3. Paste the values back into the column in Excel.

    Now when I paste the result of the formula into my database tool or text editor or whatever, I see the date as expected. What a pain in the arse for something seemingly so simple.

    One thought on “Excel And Its Infuriating Date Rigmarole

    1. I run into this problem alot when pasting things into powerpoint as it has another date formating issse (even more infuriating).This also works and cuts out the intermediate step.Create a formula =TEXT(, “MM/DD/YYYY”) where ref is the date that you want to have. This formula should return a text field. I don’t know how oracle takes dates, but excel has like 30 different ways and should have what you are looking for. This will allow you to keep from exiting and should generally make it easier to work with.

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out /  Change )

    Google photo

    You are commenting using your Google account. Log Out /  Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out /  Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out /  Change )

    Connecting to %s

    This site uses Akismet to reduce spam. Learn how your comment data is processed.