I'm retrieving data from an Oracle database into an Excel spreadsheet, the first 12 days of the month display in the format mm/dd/yyyy the remainder of the month displays correctly dd/mm/yyyy. I've tried all sorts of cell formatting without success. Does anyone have a clue what the problem could be?
Thanks in advance
Not had this with dates but had the same issue with numbers. Some numbers in a column came across as numbers and some as text and the format option wouldn't changed the text cells back to numbers.
Got round it by using the left and right text functions to split the cell contents up then the concatenate function to "reassemble" the numbers stored as text. After that was able to convert to numbers.
So I would try changing the format of all dates to General (which should display as a number)then convert them all back to the date format you want or try multiplying every cell by 1 to convert to a number then changing to the date format you want.
I've seen this before with foxpro data.
The standard format in the data is mm/dd/yyyy fixed by the database but the regional settings on the local machine are dd/mm/yyyy. The answer is to force one format or the other in your importing.
Don't know how you do that though. Is the data linked, copied and pasted, exported and imported?
Chances are some of the dates aren't formatted as number at all.
Set up a new column with =Value(Cellref) then format to date you want or...
=Date(year(=left(XX)),etc
Could you force the data to varchar using a mask on the Oracle query, instead of using time data?
select cast( table.date_column AS varchar2(30) ), etc.
Edit: use to_char, instead
Thanks for the replies, I can't access the data from home but you've given me a few things to try when I'm back at work.
This topic has been closed to new replies.
Advertisement
Advertisement
Advertisement
Advertisement
Advertisement
