+ Reply to Thread
Results 1 to 5 of 5

Excel date problem

  1. #1
    Registered User
    Join Date
    07-16-2007
    Posts
    5

    Excel date problem

    hi guys, first post.

    I`m having a problem with Excel date formatting which is really quite bizarre.

    All of a sudden, on random dates, it has started to switch the month with the day, so if I enter 10/06/2007 it will switch it to 06/10/2007. This does not happen on every instance of this particular date or even on every date you enter, just seemingly random ones. It will also not switch it if the day that you enter is greater than 12, so 15/06/2007 will be left alone.

    This has just started happening and I can't figure out why. One of my colleagues says she has encountered this problem before and resolved it by copying and pasting all of the data into a fresh Excel workbook and saving, which did work on the day I did it but it is continuing to do this.

    I have never encountered this problem before but I have not previously been a heavy user of any MS Office products.

    The cell is formatted as a date of type *14/03/2001. I have tried it as just 14/03/2001 but it doesn't make any difference. Apparently, the * means that it will not switch date orders with the operating system. The OS is set to UK dates.

    I`d appreciate any help as I have just started my job and am trying to impress by reformatting all their Excel logs to be a bit more readable, this one is making me look a bit of a twit!

  2. #2
    Registered User
    Join Date
    07-16-2007
    Posts
    5
    sorry for the double post but there doesn't appear to be an edit button on this forum.

    One other thing that i`ve just realised is happening, not sure if this will help with the diagnosis, is that when you enter a date that Excel switches around (as mentioned above), if you enter it the other way, it switches it back!
    So if I enter 02/10/2006 it switches it to 10/02/2006 but if I enter 10/02/2006 it switches it to 02/10/2006.

    This just gets weirder by the minute!

  3. #3
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Hello hermiod,

    When you say "the OS is set to UK dates" do you mean the Regional settings?

    What you describe is consistent with the worksheet cells being set with one format , i.e. dd/mm/yy and the regional settings set to the opposite, i.e. mm/dd/yy.

    If that were the case then a date like 02/10/2007 would be interpreted as whatever the regional settings dictate, i.e. 10th Feb and would display as that, given your dd/mm/yy worksheet settings. I suggest you check regional settings in the control panel and change to dd/mm/yy if necessary. At the same time make sure that all your worksheet cells are also set to dd/mm/yy

    When you input a date like 14/02/2007, if regional settings are set to mm/dd/yy this won't be interpreted as a date at all (because month is greater than 12) - excel just treats it as text - you can see this if you try to reformat it - you won't be able to because it hasn't been recognised as a number

  4. #4
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Couple of things you can try

    First select the columns then Data > text to columns > Click next twice to go to wizard page 3 and change column data format to Date > DMY

    or Check your PC settings

    Start > Control Panel > Date, Time, Language, and Regional Options > Change the date and time > time Zone > GMT.


    VBA Noob
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

  5. #5
    Registered User
    Join Date
    07-16-2007
    Posts
    5

    Thumbs up

    thanks for all the replies guys.

    I don't have access to the regional settings personally (the computers are on a company network and locked down to the hilt) but i`ve been assured by IT Services that the regional settings are correct. Not sure if I believe them though.

    The solution I have found is to set the cell formatting to text, use a macro and format the entered date to a Short Date that way. i`ve tried this with the dates I was having problems with and it seems to be ok now.

    Only problem with this method is that when you take off the date formatting, Excel turns all of the problem dates into serial dates. I have had to make another function to copy all of the column contents into another text-formatted column, then manually delete all the data in that column, set it to text format, then copy the copied data over.

    So crisis has been averted but thanks all for the replies.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1