Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Month format in EXCEL VBA (EXCEL 97/2000)

    I have a small VBA sub that reads & calculates some dates from a sheet... In the end, I "feed" the result into a DLL which is doing some more calculations.

    Learned from a German colleague this morning there was an error. I found out that the string (date) I was feeding was: 04-Mrz-2003; the DLL knows the Month only in English and thus as "Mar".
    It seems that the system settings make that happen.

    I'm looking for a way (which also works in Ex-97) to feed the month as English (and as characters: ergo as "Mar") regardless of the system language. Now of course I can program that; get the month from the string using the Month() function and then replacing the month using a CASE-structure (if Month=1 then "Jan" etc.).

    I'm just wondering if this can't be done more elegantly...

    Thanks,

    Erik Jan

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

    Re: Month format in EXCEL VBA (EXCEL 97/2000)

    Feed the month as the month number, not as the name.
    Legare Coleman

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Month format in EXCEL VBA (EXCEL 97/2000)

    Like I said... I have to feed the month as characters (e.g. "Mar")...

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

    Re: Month format in EXCEL VBA (EXCEL 97/2000)

    OK, then you do the conversion from month number to month name, something like this:

    <pre>Dim vMonths As Variant
    Dim strMon As String
    Dim datMyDate As Date
    datMyDate = DateValue("3/3/2003")
    vMonths = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
    strMon = vMonths(Month(datMyDate) - 1)
    </pre>

    Legare Coleman

  5. #5
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Month format in EXCEL VBA (EXCEL 97/2000)

    Yep, that works of course... thanks (I'd hoped there would be a function or so), in principle your proposed solution is sort-of an implied case-structure... Anyway, it works so thanks for your help!

    Erik Jan

    PS. With apologies for the delay in my response.

Posting Permissions

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