Announcement

Collapse
No announcement yet.

VBA To return week number?

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • VBA To return week number?



    hi all,

    Has anyone any idea how to get VBA to give the current week number?

    I know i can use the analysis toolpak in excel but i need this form I'm creating to give me the correct week number even if the analysis toolpak isn't installed on a machine. does anyone know of a workaround?

    Regards

    Grasshoppa

  • #2
    Re: VBA To return week number?

    Originally posted by Grasshoppa
    hi all,

    Has anyone any idea how to get VBA to give the current week number?

    I know i can use the analysis toolpak in excel but i need this form I'm creating to give me the correct week number even if the analysis toolpak isn't installed on a machine. does anyone know of a workaround?

    Regards

    Grasshoppa
    Same as WEEKNUM function, not ISO?

    =1+INT((TODAY()-(DATE(YEAR(TODAY()),1,2)-WEEKDAY(DATE(YEAR(TODAY()),1,1))))/7)
    HTH

    Bob

    Comment


    • #3
      Re: VBA To return week number?

      Is there a typo in that post?

      VBA kicks it out as "expected )".
      I'm not familar with these expressions!!!!!!!!

      anyhow.

      Comment


      • #4
        Re: VBA To return week number?

        Solved cortesy of: http://www.cpearson.com/excel/weeknum.htm


        use the following function:

        Code:
        Function VBAWeekNum(D As Date, FW As Integer) As Integer
             VBAWeekNum = CInt(Format(D, "ww", FW))
        End Function

        Comment


        • #5
          Re: VBA To return week number?

          Originally posted by Grasshoppa
          Is there a typo in that post?

          VBA kicks it out as "expected )".
          I'm not familar with these expressions!!!!!!!!

          anyhow.
          Worked fine for me. Maybe there was a pasting error!!!!!!!!!!!!!!!!!!
          Barbara - aka The Cat Lady

          Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us, unless requested by me, will be deleted.

          Comment


          • #6
            Re: VBA To return week number?

            Bob's solution works OK, but it's a formula not VBA, which is what Grasshoppa was looking for.
            Hope that Helps

            Roy

            New users should read the Forum Rules before posting

            For free Excel tools & articles visit my web site

            If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

            RoyUK's Web Site

            royUK's Database Form

            Where to paste code from the Forum

            About me.

            Comment


            • #7
              Re: VBA To return week number?

              Originally posted by royUK
              Bob's solution works OK, but it's a formula not VBA, which is what Grasshoppa was looking for.
              Ah so, Grasshoppa which is why pasting the Excel formula into VBA wouldn't work.

              Guess that old saying, "two wrongs don't make a right" is true. Nice link though.
              Barbara - aka The Cat Lady

              Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us, unless requested by me, will be deleted.

              Comment


              • #8
                Re: VBA To return week number?

                Simply

                Format(Date, "ww")

                Comment


                • #9


                  Re: VBA To return week number?

                  Originally posted by vaasha View Post
                  Simply

                  Format(Date, "ww")
                  So simple Thank you vaasha

                  Comment

                  Working...
                  X