Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Confuggulated XL (W97, WinNT4)

    It seems I have a very confuggulated program here. My machine (control panel, regional settings or doubleclick on time in system tray) says today's date is 6 March 2002. My today's date macro ...

    Sub TodaysDate()
    Dim dDate As String
    dDate = Format(Now, "dd/MM/yy")
    ActiveCell = dDate
    End Sub

    ... says it's 3 June 2002.

    If I run this macro with a breakpoint to check what it's doing, it says its 6 March right up to when it pastes the string in the cell ... when it's 3 June again.

    If I select a blank cell, go to Format, Cells, Number, Date and choose one with the month as a word, the sample at the top of the Format Cells window says it's 3 June.

    If I change the date (doubleclick on the time in the system tray) to one beyond the 12th of the month and insert the date again, it realises it's actually March, not June.

    But as soon as I go back to the real date it switches back to 6 June again.

    <img src=/S/confused.gif border=0 alt=confused width=15 height=20> <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23> <img src=/S/headthrob.gif border=0 alt=headthrob width=15 height=15>

    Anyone else come across this?!
    Beryl M


  2. #2
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Confuggulated XL (W97, WinNT4)

    try dDate = Format(Now, "dd/mmmm/yy") and see whether that helps

    the problem you're running into is American vs the rest of the world date formatting - once you get a month over 12 excel knows that it's doing something wrong but below that it has no way of telling.

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Confuggulated XL (W97, WinNT4)

    Thanks for the suggestion, which will probably work, but unfortunately some of the places I use that macro are inserting dates into a bigger system which has to have them in the dd/MM/yy format ... <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15>
    Beryl M


  4. #4
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Confuggulated XL (W97, WinNT4)

    Have you checked the cell formatting of the actual spreadsheet cell (through Format:Cells, or rightclick:format cells?)

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Confuggulated XL (W97, WinNT4)

    Actually, the strangest thing is that it always used to work fine, until my XL was 'verified' (we have one of the those Novell distributed software systems ...), at which point I lost my personal toolbar completely and only got my personal.xls to be recognised again after a struggle. Plus the date is back to front ...

    Don't you just love Microsoft (and Novell) ...?!
    Beryl M


  6. #6
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Confuggulated XL (W97, WinNT4)

    Umm, the macro actually formats the cell as it runs. I know it doesn't specifically say so, but when I cleared the formats from the cell (Edit, clear, formats) then ran the macro, the format afterwards is the Custom dd/MM/yy.
    Beryl M


  7. #7
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Confuggulated XL (W97, WinNT4)

    have you checked the regional settings for Windows?

    Edited immediately.

    Yes you have - see, I am reading your posts, honest! I'm fast running out of ideas. I'll carry on thinking but you'll have to hope others can jump in!

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Confuggulated XL (W97, WinNT4)

    What happens if you shorten your macro to:
    activecell = format(Date,"dd/mm/yy")?
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Confuggulated XL (W97, WinNT4)

    Yes - and tried the old trick of changing the setting, applying it, then changing it back and applying it again (standard practice for us hard-done-by Brits!). Windows has never argued about the date - only XL. Even Word is quite happy to admit that it's only March, not June, with the same date layout (tested using { date @ "dd/MM/yy" } field code)!

    <img src=/S/weep.gif border=0 alt=weep width=21 height=16>
    Beryl M


  10. #10
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Confuggulated XL (W97, WinNT4)

    Good idea - but exactly the same effect! <img src=/S/flee.gif border=0 alt=flee width=25 height=25>
    Beryl M


  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Confuggulated XL (W97, WinNT4)

    As a matter of interest, what is this for? By which I mean, why do this rather than just pressing Ctrl+; to get the date?
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Confuggulated XL (W97, WinNT4)

    <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15> Well, it's quite simple, really.

    I didn't know you could.

    <img src=/S/drop.gif border=0 alt=drop width=23 height=23>

    Thanks, Rory.

    ... However, Ctrl-; also puts in 3 June ....
    Beryl M


  13. #13
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Confuggulated XL (W97, WinNT4)

    Well that's just plain weird! (VBA I sort of expect to do strange things with dates but Ctrl+; has always worked for me.)
    I don't know if it makes a difference, but are you running a local copy of Excel or a networked copy?
    Regards,
    Rory

    Microsoft MVP - Excel

  14. #14
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Confuggulated XL (W97, WinNT4)

    One other thing that I guess might make a difference - is your system's default locale set to United States or United Kingdom?
    Regards,
    Rory

    Microsoft MVP - Excel

  15. #15
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Confuggulated XL (W97, WinNT4)

    UK all down the line. And the installation is local, but updated from the network every time you open it using the Novel Distributed Software gismo I mentioned above.

    I tend as a result to use a local shortcut to open it because the NDS way tends to reset everything, and I don't like that.
    Beryl M


Page 1 of 2 12 LastLast

Posting Permissions

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