Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Date format problems (2000sr1a)

    I'm having a lot of problems with dates entered into the same spreadsheets, usually, but not always, from different computers, ending up stored as different formats. I thought I'd cracked it when I started using the numberformat property, only to find that this returns m/d/yy regardless of the value displayed by Format - Cells, and regardless of which format the date was entered in. If you set this property in vb, however, then it will return the value you set. Confused, I am. So now I have a load of dates that I can reformat easily (where the "month">12), and a load of dates I'm not sure what to do with, though some of them can be changed through internal consistency checks.

    So, 2 questions. Firstly is there any way to find out the format a date was entered in, and secondly, how can I force all dates entered to be recognised as being entered as dd/mm/yy, regardless of which machine they are entered on (ie force a spreadsheet or application dependent locale)?

    thanks

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date format problems (2000sr1a)

    Excel VBA speaks American. This gives rise to problems when processing dates as you've discovered.

    This page gives some explanation.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date format problems (2000sr1a)

    Its a little hard to understand exactly what your problem is without seeing the sheet. There are a number of things that could cause the symptoms you describe. However, the most likely problem is that some of those dates are entered as text, not as date values. This can happen if the cell is formatted as text before the date is entered, or if there is a space in front of the date when entered. You can find out if this is the problem by inserting an empty column next to the column containing the dates (the formula below assumes the dates are in column A), and entering the following formula in the cell next to the first date and fill it down.

    <pre>=ISTEXT(A1)
    </pre>


    That formula will display True for all dates that are entered as text.

    To fix most of this problem, do the following:

    1- Insert an empty column next to the dates and format this column as Date with your favorite date format.

    2- Enter the formula below in the cell next to the first date and fill the formula down the column. Again, this formula assumes that the first date is in cell A1.

    <pre>=IF(ISTEXT(A1),DATEVALUE(TRIM(A1)),A1)
    </pre>


    3- Select all of the dates in the new column and then click on Copy in the Edit menu.

    4- Click on "Paste special" in the Edit menu.

    5- In the Paste section of the dialog box click on the radio button next to "Values". Then click on OK.

    6- You should now be able to delete the original column, or copy the dates and paste them back into the original column if there are other cells in that column that you want to keep.

    This should convert most or all of the dates into date values. There are some other things that can cause dates to be entered as text values that the above procedure will not fix. We would have to see the sheet to know what caused the problem and how to fix it. If this does not fix your problem, could you attach a copy of the workbook with everything but the date column deleted?
    Legare Coleman

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Date format problems (2000sr1a)

    Maybe I'm missing something, BUT

    Is it a FORMAT issue or are the date values (serialdates) incorrect?
    If it just a FORMAT (display issue), just change the format (Format cells - number -custom) to (no quotes):
    "dd/mm/yy"

    It does NOT matter what format it was entered in.
    If you enter in Europe Jan 5, 2003 as 5/1/2003 or in US as 1/5/2003 BOTH will be translated in excel (assuming your regional settings are set appropriately) as the serial date 37626. SO on you machine you can format it anyway you want.

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Date format problems (2000sr1a)

    There are 3 problems.
    1) Dates have been entered on different machines (to my file on the network) but always in the format d/m/yy. If the locale of the machine was US, 1/5/02 was interpreted as the 5th of Jan, and if the locale of the machine was UK then it was interpreted as the 1st of May. This leaves me with 2 sorts of problems. The first, which Legare alluded to, is that "impossible" dates entered under US locale aren't formatted as dates, so 15/5/02 remains as text. These are easy to deal with. The second group are those which are plausible in either format, so if they were entered on a computer with locale set to US the serial numbers will be wrong. I need to find these and deal with them. A number of them can be found through looking for logical inconsistencies, such as babies seen before their date of birth, but many cannot.
    2) With regard to this second group, I don't understand why the vba property .numberformat returns "m/d/yyyy" regardless of whether the cells are formatted (according to the information accessed via the menu Format-Cells-Number) as "mm/dd/yyyy" or "dd/mm/yyyy", or of how the dates were entered (I've tried). The string returned by .numberformat does change, however, as if you set this value, it will return the value you've set when later queried. The answer to this may lie in the page Jan directed me to, which I'm struggling through as the concepts are unfamiliar.
    3) How can I stop this happening again - ie how can I force the locale to be UK? If necessary, I could just check the locale in an on open routine, and not open the file if it wasn't set to UK. Not sure how to do this without triggering the macro warning, however, or worse, being ignored if security is set to high. Is the locale settable from vba, or can I set the office language from vb and see if that works (though from my experience I doubt it)?

    thanks again

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date format problems (2000sr1a)

    1) If you want users to enter dates in d/m/yy format regardless of the input locale, you could set the number format of the input cells to text. Use a formula in another column to convert this to a date. Alternatively, you could use separate input cells for day, month and year.

    2) The VBA NumberFormat property returns a US style format; to see the "local" format, use the NumberFormatLocal property. For example, my PC has Dutch settings. If I enter today's date in a cell, ActiveCell.NumberFormat is "m/d/yy", and ActiveCell.NumberFormatLocal is "dd-mm-jjjj" (the j is the first letter of the Dutch word for year).

    3) I wouldn't even try changing the user's input locale, or the Office language. Very confusing for the user.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Date format problems (2000sr1a)

    Another way is to set the format to something like "mmm d, yyyy" so there is a check for the user to see if what he/she entered is correct. This format is more universal. If you enter in 5/1/2003 in Europe or 1/5/2003 they both will display as "Jan 5, 2003"

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •