Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: VBA To return week number?

  1. #1
    Join Date
    19th September 2004
    Location
    Chester
    Posts
    148

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    2nd November 2005
    Location
    Wessex
    Posts
    1,267

    Re: VBA To return week number?

    Quote 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)

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    19th September 2004
    Location
    Chester
    Posts
    148

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    19th September 2004
    Location
    Chester
    Posts
    148

    Re: VBA To return week number?

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


    use the following function:

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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    2nd August 2004
    Location
    Ontario - Canada
    Posts
    1,452

    Re: VBA To return week number?

    Quote 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!!!!!!!!!!!!!!!!!!

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    17,442

    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

    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

    About me.

  7. #7
    Join Date
    2nd August 2004
    Location
    Ontario - Canada
    Posts
    1,452

    Re: VBA To return week number?

    Quote 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.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    21st December 2012
    Location
    Praha
    Posts
    1

    Re: VBA To return week number?

    Simply

    Format(Date, "ww")

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Return Monday Of Given Week
    By samfax in forum EXCEL HELP
    Replies: 10
    Last Post: February 28th, 2012, 22:05
  2. Return Corresponding Data For Week Number
    By mswuk in forum EXCEL HELP
    Replies: 2
    Last Post: January 27th, 2008, 08:27
  3. look up day in calendar array return week
    By kai_ge in forum EXCEL HELP
    Replies: 4
    Last Post: August 10th, 2006, 21:54
  4. Return Weekday, or Day of Week
    By Dave Hawley in forum Excel FAQ
    Replies: 0
    Last Post: October 3rd, 2005, 17:07
  5. Return Week Number
    By Dave Hawley in forum Excel FAQ
    Replies: 0
    Last Post: October 3rd, 2005, 16:26

Bookmarks

Posting Permissions

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