In this article, the user will easily obtain the month number by using the weekday and year numbers. This will allow the user to generate the output, as shown below: The specified command will accept the week number and year number as an argument. GetMonthFromWeekNumber = Month(targetWeekStart)Īgain go to the Excel sheet, and type ‘=GetMonthFromWeekNumber(10,2023)’. TargetWeekStart = DateAdd("ww", weekNum - 1, firstWeekStart) ' calculate the date and store it in the above variableįirstWeekStart = DateAdd("d", 1 - Weekday(firstDayOfYear, vbMonday), firstDayOfYear) ' declare the variable to store first week starting date 'define function to get month from provide weeknumberįunction GetMonthFromWeekNumber(weekNum As Integer, yearNum As Integer) As Integer ' declaring required variablesįirstDayOfYear = DateSerial(yearNum, 1, 1) This will open the code area, as highlighted below:Ĭopy the below given code, to the code area: In the opened dialog box, select “Insert”, and then click on the “Module” option as highlighted in the below image: The above step will open a “Microsoft Visual Basic for Applications” dialog as depicted below: To open the code editor, go to the “Developer”→ Visual Basic, under the “code” section, as shown in the below image. To understand the process of using the VBA code, to evaluate month numbers from weekend and year, consider an empty worksheet. Consider the below-given image for reference.Įxample 2: To get month from week number and year in Excel by using the VBA code: Step 1: ![]() This will display the result in the current row and will shift the control to the next box. This is the month that contains the specified week number (in cell B2) of the specified year (in cell C2). This provides the date for the first day of the first week of the specified year.įinally MONTH() method will calculate the month for the date calculated in the above step. It is compulsory because Excel's week numbering system considers the week containing January 1 as the first week of the year.ĭATE(C2,1, B2,*7-2)-WEEKDAY(DATE(B2, 1, 3)) − this part will subtract the day of the week for January 3 of the specified year from the date calculated in step 1. WEEKDAY(DATE(B2, 1, 3)) − this part of the formula calculates the day of the week for January 3 of the specified year ( referenced in cell C2). Go to the D2 cell and type the mentioned formula to obtain the required month “=MONTH(DATE(C2,1,B2,*7-2)-WEEKDAY(DATE(B2, 1, 3)))” as highlighted in the below image.ĭATE(C2,1, B2,*7-2) − this part of the formula creates a date for the first day of the specified year (referenced by using the C2 cell). Consider the below-provided worksheet, with some sample data − To understand the process of generating month numbers from the provided week number and year. Example 1: To get month from week number and year in Excel by using the formula: Step 1: ![]() This article contains stepwise explanations for both the discussed practices. ![]() Please note that VBA is a programming language that can work only when written properly within a proper format. The second method guides the learner about the use of VBA code. ![]() The first example guides the learner to use the predefined methods, in the form of user-defined formulas. This article describes two strategies to achieve the required task. This article briefs learners about the way to generate the month-from-week number and year in Excel.
0 Comments
Leave a Reply. |