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:
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.
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.