Results 1 to 7 of 7
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set a Default Date Format Followup (2000 SR-1)

    Can you tell us what is the date format that your are trying to change FROM? If we know that, then we should be able to write a very simple VBA procedure that would reformat all the dates in the worksheet - or workbook if necessary.
    Gre

  2. #2
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Flower Mound, Texas, USA
    Posts
    100
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Set a Default Date Format Followup (2000 SR-1)

    On July 1, I submitted <post#=270045>post 270045</post#> and received great responses and everything worked as it should.

    I now have a macro that converts the date in the active cell to this format: mmmm yyyy (July 2003). Is it possible to have the macro apply to selected cells, rather than only the active cell? In a long list, it is quite time consuming to format each cell individually.

    Diane

  3. #3
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Flower Mound, Texas, USA
    Posts
    100
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set a Default Date Format Followup (2000 SR-1)

    It works exactly as I had hoped! Thanks!

    I didn't realize how extremely long my original post ended up being. When you press Ctrl+A to select all, you don't have a good perspective of exactly how much you're selecting. Sorry.

    I guess that since I couldn't locate the original post, I assumed it was no longer available. It didn't occur to me that just because I couldn't find it in the listing, it would still be there. That's the first time I've used the "1 Click Tag Panel." Hope it worked okay. Thanks for letting me know about this.

    Unkamunka, thanks for your quick reply.

    Diane

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Set a Default Date Format Followup (2000 SR-1)

    <P ID="edit" class=small>(Edited by JohnBF on 28-Jul-03 12:41. Coding errors corrected.)</P>Modify the macro as follows:

    Sub SetDateFormat()
    Dim rngCell as Range
    For Each rngCell in Selection
    rngCell.Value = Date ' eliminate this line if the date is already there
    rngCell.NumberFormat = "mmmm yyyy"
    Next rngCell
    End Sub
    -John ... I float in liquid gardens
    UTC -7DS

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

    Re: Set a Default Date Format Followup (2000 SR-1)

    Slightly less complicated and more efficient:

    <pre>Public Sub SetDateFormat()
    Selection.NumberFormat = "mmmm yyyy"
    End Sub
    </pre>

    Legare Coleman

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Set a Default Date Format Followup (2000 SR-1)

    Much better. Had no idea that Selection directly inherited it's underlying Object's Properties, though now it seems obvious.
    -John ... I float in liquid gardens
    UTC -7DS

  7. #7
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Flower Mound, Texas, USA
    Posts
    100
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set a Default Date Format Followup (2000 SR-1)

    Thanks, Legare!

    Diane

Posting Permissions

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