In this example, we will create a custom function to find the week number for the specified date.
For this example, sample data consists of employee’s attendance data which includes date, employee id and attendance status.
We want to find the week number for a particular date.
In this example, we have used both in built functions “WEEKNUM” and custom function “Display_WeekNum” to calculate the week number for the given date.
Excel has in built function “WEEKNUM” to find the week number.
Syntax of WEEKNUM function
Date parameter specifies the date for which one wants to find the week number and FirstDayOfWeek specifies the day which needs to be considered as the first day of the week.
We have also build custom function “Display_WeekNum” to find the week number for the given date.
In the “Display_WeekNum” custom function, we have used WorksheetFunction to access the inbuilt WEEKNUM function of the Excel. This function takes date as input parameter and returns the week number. By default, this function considers Monday as first day of the week.
Please follow below for the code
Option Explicit Function Display_WeekNum(DefinedDate As Date) Display_WeekNum = WorksheetFunction.WeekNum(DefinedDate, vbMonday) End Function
We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at firstname.lastname@example.org