Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date formatting in Text worksheet function

    I want to use the following worksheet function in my Excel 97 spreadsheet (Win 95):

    ="Status " &TEXT(TODAY(); "dd mmmm yy")

    The output is: Status 08 maart yy

    maart is the Dutch word for March, this is correct, but what's puzzling me is why the year "yy" is not converted into 01, but remains "yy". I tried to use NOW() instead of TODAY() but with the same outcome.

    Anyone an idea how to circumvent this (probable) bug in Excel?

  2. #2
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date formatting in Text worksheet function

    Just out of interest, I tried it here on Excel 2K.
    I got an error unless I used a comma rather than a semi-colon after the () - i.e. ="Status " &TEXT(TODAY(),"dd mmmm yy") - but does show the full date you are looking for..

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Date formatting in Text worksheet function

    Same result as Leif here, have you tried "dd mmmm yyyy" just to see if that works.

    AC

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date formatting in Text worksheet function

    Leif,

    The semicolon or comma has to do with the regional settings in Windows (I use ; as the listseparator, you are using the comma as listseparator, that's the reason for your error).
    I tried the format dd mmmm yyyy and then all 4 yyyy are displayed instead of the year. I asked a colleague to do it on a different computer with the same result. I try it out this evening at home (where I use Excel 2000), but I am afraid it's an Excel 97 bug.

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Date formatting in Text worksheet function

    It works perfect for me (with the exception of the semicolon) on Excel 97 SR1.

    If you enter a date and set the format to "dd mmmm yy" does it show as you would expect ?

    AC

  6. #6
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date formatting in Text worksheet function

    >>...regional settings in Windows... - sorry, obvious when you think about it [img]/S/doh.gif[/img] .

    Try:
    ="Status " &TEXT(TODAY();"dd mmmm ") &TEXT(TODAY();"yy")

  7. #7
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date formatting in Text worksheet function

    Thanks to your comments and suggestions, I puzzled it out. Although I am using an English version of Excel 97, the language (in Windows) is set to Dutch (as default) and consequently I had to use the format "dd mmmm jjjj", instead of "dd mmmm yyyy". You have to know that d = day = dag (in Dutch); m = month = maand (in Dutch) and y = year, but "year" in Dutch is "jaar", so I have to use "jjjj".
    It's not the first time that I get confused due to Excel and Windows mixing up languages and other regional settings.
    Thanks to all of you.

Posting Permissions

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