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