Viewing 6 posts - 1 through 6 (of 6 total)
  • Todays Excel challenge
  • Dibbs
    Free Member

    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

    cbrsyd
    Free Member

    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.

    muddy_bum
    Free Member

    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?

    Fresh Goods Friday 696: The Middling Edition

    Fresh Goods Friday 696: The Middlin...
    Latest Singletrack Videos
    TheSouthernYeti
    Free Member

    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

    mogrim
    Full Member

    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

    Dibbs
    Free Member

    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.

Viewing 6 posts - 1 through 6 (of 6 total)

The topic ‘Todays Excel challenge’ is closed to new replies.