getting the week number automatically

October 2017


getting the week number automatically




Issue


I need to get the week number automatically... Let say I enter the date in Cell A2 then I want Cell B5 to show the respective week number. Let say for example if today is 18/8/2010 and week should be 33 I recorded a macro and entered it into sheet1 code but nothing happened... Can someone help me correct this code? Thanks

Sub week2()
    ActiveCell.FormulaR1C1 = "=WEEKNUM(R[-3]C)"
    Range("B6").Select
End Sub

Solution

  • Used the command line below;

Sub week2()
    ActiveCell.FormulaR1C1 = "=TRUNC(TRUNC((A1-DATE(YEAR(A1),1,0))+6)/7)"
    Range("B6").Select
End Sub

Note


Solved by rizvisa1
Published by aakai1056. Latest update on August 26, 2010 at 02:25 AM by aakai1056.
This document, titled "getting the week number automatically," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).