getting the week number automatically

December 2016


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

Related :

This document entitled « getting the week number automatically » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.