Which week is the fetch date of the month

Given that we have a function called WEEK() to retrieve the WEEK of the year in which the date is located, how do we obtain the WEEK of the month in which the date is located?

Use DATEINMONTH() to obtain the date of the first day in the month. Use WEEKDAY() to obtain the number of the first day in the month. Add the number of days for querying the date to the number of days for querying the date.

1) Insert the formula RANGE(date(2018, 12, 1), today()) in cell A1 and set the extension direction of the cell to Vertical:

Insert the formula in cell B1:ROUNDUP((DAY(A1) + (IF(WEEKDAY(DATEINMONTH(A1, 1))) = 0, 7, WEEKDAY(DATEINMONTH(A1, 1)))) - 1) / 7, 0)

Note: The calculation result of this formula starts from Monday. If the calculation starts from Sunday,the formula will change to: ROUNDUP((DAY(A1) + (IF(WEEKDAY(DATEINMONTH(A1, 1))) = 0, 7, WEEKDAY(DATEINMONTH(A1, 1))))) / 7, 0)) ROUNDUP((DAY(A1) + (IF(WEEKDAY(DATEINMONTH(A1, 1))) = 0, 7, WEEKDAY(DATEINMONTH(A1, 1)))

3) Save the template and click Pagination Preview.

For the completed template, see: %FR_HOME%\webapps\webroot\WEB-INF\reportlets\doc-EN\Parameter\Which_week_is_the_fetch_date_of_the_month.cpt

Click to download the template:

Which_week_is_the_fetch_date_of_the_month.cpt

Common date references: Common date processing functions.

1) PC

2) Mobile