Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Jun 2004
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date format (VBA)

    I am trying to convert from the month number to the month name.
    This code returns a 9 for September.
    Me.txtCurMonSt = DatePart("m", Now())
    How do I convert the 9 to "September" to use in a label in a report.

    Thanks,
    Nick

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date format (VBA)

    The easiest way is

    Me.txtCurMonSt = Format(Date, "mmmm")

    m = month number 1 - 12
    mm = month number with leading zero 01 - 12
    mmm = abbreviated month name Jan - Dec
    mmmm = full month name January - December

  3. #3
    2 Star Lounger
    Join Date
    Jun 2004
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date format (VBA)

    What if I want to take the
    Me.txtCurMonSt = Format(Date, "mmmm") and go back one month.
    Like me.txtcurMonSt would equal August?

    Thanks,
    Nick

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date format (VBA)

    Me.txtCurMonSt = Format(DateAdd("m", -1, Date), "mmmm")

    DateAdd("m", -1, Date) adds -1 month to the current date, i.e. subtracts 1 month.

  5. #5
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Date format (VBA)

    Starting with VBA 6 (Office 2000 and higher), there is a convenient new MonthName function as well:

    Monthname(1, False) => "January"
    Monthname(8, True) => "Aug"

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date format (VBA)

    But then you have to extract the month number from the date first.

  7. #7
    2 Star Lounger
    Join Date
    Jun 2004
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date format (VBA)

    thanks HansV.
    it worked perfect

  8. #8
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Date format (VBA)

    Oh, I didn't look inside the original poster's DatePart() function to see that it was the current date. You are so right: Format is much better in this case.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date format (VBA)

    Still, it's good to draw attraction to the MonthName function. I suspect many users have never noticed it. It can be very useful.

Posting Permissions

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