Consulting

Results 1 to 8 of 8

Thread: Problem Formatting Date for Display

  1. #1

    Problem Formatting Date for Display

    I just encountered a peculiar discrimination.
    I wanted to extract a date from a cell, then include it in a Msgbox display. The cell has the date displayed as "mm/dd/yy". Note that there are just 2 year digits.
    My VBA code looked like:

    BillDate = Range("A1")
    Msgbox "This is the date: " & BillDate
    What is displayed is
    This is the date: 05/22/2005 (A 4-digit year)
    So I altered my code above to look like:

    BillDate = Format(Range("A1"), "mm/dd/yy")
     Msgbox "This is the date: " & BillDate
    and I got the same results with a 4-digit year. So I changed the code to be:

    BillDate = Range("A1")
      Msgbox "This is the date: " & Format(BillDate, "mm/dd/yy")
    and it worked!
    Why is it that I can't format the date when putting it into a string variable, then display it, but it works when I format it in the Msgbox statement??

  2. #2
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    Your second code works fine for me.

    Are you sure BillDate is a String Data Type and not a Date Data Type?

  3. #3
    DRJ, you guessed it! My variable IS a type date. Just didn't think about that. Seems so obvious when you point it out. Thanx.

  4. #4
    Moderator VBAX Master geekgirlau's Avatar
    Joined
    Aug 2004
    Location
    Melbourne, Australia
    Posts
    1,464
    Location
    Now if there was only a way to stop you Yanks putting your dates in backwards ...

  5. #5
    Administrator
    Chat VP VBAX Guru johnske's Avatar
    Joined
    Jul 2004
    Location
    Townsville, Australia
    Posts
    2,872
    Location
    Quote Originally Posted by geekgirlau
    Now if there was only a way to stop you Yanks putting your dates in backwards ...
    I'll second that - it's VERY confusing to the rest of the world when there are only numbers involved, for numbered dates we always use the day-month-year (smaller periods of time to larger) convention and only use month-day-year when the month can be written in full - e.g. May 1st 2005 (which can then also be used as 1st May 2005)
    You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you

    The major part of getting the right answer lies in asking the right question...


    Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.

  6. #6
    I don't think of myself so much as a yank, but more of a tug.
    As for date format, I personally ALWAYS write a full date as DD MMM YY. Well, ALMOST always. When I omit the year, then I write MMM DD ... sorry 'bout that.
    Speaking of dates, I noticed that the date on this post is the 23rd, but the post before it is on the 24th. Migawd, I'm getting posts from the future!!!

  7. #7
    Administrator
    Chat VP VBAX Guru johnske's Avatar
    Joined
    Jul 2004
    Location
    Townsville, Australia
    Posts
    2,872
    Location
    Quote Originally Posted by Cyberdude
    Speaking of dates, I noticed that the date on this post is the 23rd, but the post before it is on the 24th. Migawd, I'm getting posts from the future!!!
    Hey, yup - it's future-land here (sumpin to do with the international date-line I think)
    You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you

    The major part of getting the right answer lies in asking the right question...


    Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.

  8. #8
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by Cyberdude
    I don't think of myself so much as a yank, but more of a tug.
    As for date format, I personally ALWAYS write a full date as DD MMM YY. Well, ALMOST always. When I omit the year, then I write MMM DD ... sorry 'bout that.
    I always push for the ISO standard yyyy-mm-dd, nobody can get confused then. Especially important in VBA where those darn Yanks force their date format on us

Posting Permissions

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