Problem with date format on excel sheet

Discussion in 'I.T.,Cameras and MP 3 players' started by Madilla, Sep 17, 2010.

  1. Madilla

    Madilla Frequent Poster

    Posts:
    301
    I have been sent a file with dates. Some of the dates are appearing in the incorrect format. One date shows 8/10/2010 when it should be 10/8/2010. When I look at format of cell it shows English UK.Other dates appearing correctly have just general format. The sender of the file(US subsidary) confirms it displays correctly on her side.Wondering if anyone can advise where the problem might arise from. Many Thanks
     
  2. alaskaonline

    alaskaonline Frequent Poster

    Posts:
    488
    does right click on the number and then "format cells" and then "date" not work for you? what excel version are you using? i have 2007 and 16 options.

    you can also try the trick to copy/ paste it into a text pad and then back into a new excel sheet. worked for me before - why? - i honestly don't know.
     
  3. Madilla

    Madilla Frequent Poster

    Posts:
    301
    I have no problem with working around this problem to fix on this file-changing format to US date sorts it. The difficulty is that this is date critical data and had the problem not been noticed could cause a lot of difficulties. I need to know why it is happening to ensure that in futre I can be confident in the integrity of my data.
     
  4. marksa

    marksa Frequent Poster

    Posts:
    367
    maybe try changing the settings of your pc to US keyboard and regional settings in windows control panel before opening the file, and then see if it opens okay. slight pain I guess, but if it solves your problem it's probably worth it.
     
  5. ajapale

    ajapale Moderator

    Posts:
    7,677
    are you sure that all your dates are in the Julian Date form? It is possible that some are simply in text format?
     
  6. allthedoyles

    allthedoyles Frequent Poster

    Posts:
    1,017
    Hope you understand that 8/10/2010 is August 10th 2010 in the US and therefore if your excel file came from USA , the dates are correct.

    Check that you are both using the same MS Office Excel , as MS 2003 is way different than MS 2007 .
     
  7. mathepac

    mathepac Frequent Poster

    Posts:
    5,584
    It seems strange that some cells with dates are OK and others are not. This would lead me to suspect that the originator does not know how to specify and maintain data formatting, validation and integrity rules on a column by column basis.

    If the data-items in certain fields are critical, you may have no option but to design a template with the rules you wish to apply embedded and insist it gets filled out as you want, with supporting verification documentation if necessary.

    As pointed out above, system-wide settings in the control panels may effect formatting in certain cells, I'd be surprised if the could override the application settings, but right now I have no way of checking this (no Windows machines in the house, TG). Again if necessary, insist that the spread sheet is completed and maintained on a system with the same region settings you have / need.
     
  8. Crugers

    Crugers Frequent Poster

    Posts:
    444
    Last edited: Sep 18, 2010
    I've found that this issue usually happens with data that is not created in Excel but exported/imported into Excel. In your case it might be that the data is imported into Excel in the States from a Txt file or CSV file. When it is done in the States, Excel gets it "right" - file is in mm/dd/yyyy format and is imported into Excel that expects and understands mm/dd/yyyy... That would explain why your colleague in the States says it is OK for her...

    I export from Quickbooks to Excel and the 'date' column inevitably exports from Quickbooks as US date format (mm/dd/yyyy). When it arrives in Excel the dates for the first 12 days of any month display as dates and look as if they are dd/mm/yyy but Excel understands them as mm/dd/yyyy. (If you want to check what way Excel is understanding the date data you can use the "=CELL()" formula to test it i.e. to test date data in say cell A1 type =CELL("format",A1) in an empty cell. If the result is D4 Excel thinks it is one of the many Month/Day/Year formats, D1 means it thinks it is some type of Day/Month/Year format. There are other results but I don't think they are relevant here. Search Help for "=CELL()" to find listing or them...)

    A quick check on the date data column, by making the 'date' column twice as wide, will show whether Excel recognises all the data as dates. Entries that are recognised as dates should right align and the rest, unrecognised 'dates', will left align in the column.

    SUGGESTED SOLUTION:
    When you open the file, first thing to do is select the date column only. Then use "Text to Column".

    In Excel 2003:
    Go to “Data”: “Text to Columns” and the “Convert Text to Columns Wizard” should start. Click “Next” and “Next” to get to Step 3 of 3 and in the "Column data format" (top right) select the radio button for “Date” and select “MDY” from the drop down and then click “Finish”. Now the dates should all be recognised as dates (and will right align in the column) but will display in Month/Day/Year format. You can then select them all and format them all into Day/Month/Year format.
    In Excel 2007:
    It is almost the same -
    Go to “Data” tab: “Text to Columns” and the “Convert Text to Columns Wizard” should start. Click “Next” and “Next” to get to Step 3 of 3 and in the "Column data format" (top left) select the radio button for “Date” and select “MDY” from the drop down and then click “Finish”. Now the dates should all be recognised as dates (and will right align in the column) but will display in Month/Day/Year format. You can then select them all and format them all into Day/Month/Year format.
    Phew...
    It seems complicated and long winded but once you have done it a couple of times it is "simples" tch!
    Hope this works for you...
     
    Last edited: Sep 18, 2010