Setting formulas in Kintone, you can get the monthly week number, which restarts from 1 at the beginning of every month, for the date you enter.
This article provides the example settings with formulas.

Open the Form tab in the App Settings and place the following six fields on the app form.

• (1) "Date" field
• (2) "Calculated" field
• (3) "Calculated" field
• (4) "Text" field
• (5) "Calculated" field
• (6) "Text" field

Open the settings screen of each of the fields you have placed on the app form, and set the field name, field code, and formula.
Overview of Field Codes

Setting up each field as described below makes field (6) display the monthly week number for the date entered in field (1).
Fields (2) to (5) are used for intermediate calculations.

This field is used for entering the date for which you want to get the monthly week number.

• Field name: Date
• Field code: Date

You do not need to set a formula for this field.

This field displays the day part of the date entered in field (1) (the number of days elapsed since the first day of the month).

• Field name: Date (Day part)

• Field code: Date_Day_part

• Formula: Set the following formula.

  Copy

  The content has been copied.

  DATE_FORMAT(Date, "d", "Asia/Tokyo")

This field calculates the first day of the month in UNIX time format (numerical value) based on the date entered in field (1).

• Field name: First day of the month

• Field code: First_day_of_the_month

• Formula: Set the following formula.

  Copy

  The content has been copied.

  Date-((Date_Day_part-1)*60*60*24)

This field calculates the first day-of-week of the month based on the first day of the month calculated in field (3).

• Field name: First day-of-week of the month

• Field code: First_day_of_week_of_the_month

• Formula: Select Calculate automatically and set the following formula.

  Copy

  The content has been copied.

  IF( ( ( (First_day_of_the_month / 60 / 60 / 24) - 0) / 7 ) - ROUNDUP(( ( (First_day_of_the_month / 60 / 60 / 24) - 0) / 7 ) , 0) = 0 , "Thursday",
      IF( ( ( (First_day_of_the_month / 60 / 60 / 24) - 1) / 7 ) - ROUNDUP(( ( (First_day_of_the_month / 60 / 60 / 24) - 1) / 7 ) , 0) = 0 , Friday",
        IF( ( ( (First_day_of_the_month / 60 / 60 / 24) - 2) / 7 ) - ROUNDUP(( ( (First_day_of_the_month / 60 / 60 / 24) - 2) / 7 ) , 0) = 0 , "Saturday",
          IF( ( ( (First_day_of_the_month / 60 / 60 / 24) - 3) / 7 ) - ROUNDUP(( ( (First_day_of_the_month / 60 / 60 / 24) - 3) / 7 ) , 0) = 0 , "Sunday",
            IF( ( ( (First_day_of_the_month / 60 / 60 / 24) - 4) / 7 ) - ROUNDUP(( ( (First_day_of_the_month / 60 / 60 / 24) - 4) / 7 ) , 0) = 0 , "Monday",
              IF( ( ( (First_day_of_the_month / 60 / 60 / 24) - 5) / 7 ) - ROUNDUP(( ( (First_day_of_the_month / 60 / 60 / 24) - 5) / 7 ) , 0) = 0 , "Tuesday",
                IF( ( ( (First_day_of_the_month / 60 / 60 / 24) - 6) / 7 ) - ROUNDUP(( ( (First_day_of_the_month / 60 / 60 / 24) - 6) / 7 ) , 0) = 0 , "Wednesday","N/A"
                )
              )
            )
          )
        )
      )
    )

Based on the first day-of-week of the month calculated in field (4), this field calculates the monthly week number for the date displayed in field (2).

• Field name: Monthly week number

• Field code: Monthly_week_number

• Formula: Set the following formula. As an example, this formula assumes that the start of a week is Sunday.

  Copy

  The content has been copied.

  IF(First_day_of_week_of_the_month="Sunday", IF(Date_Day_part<=7, 1, IF(ROUNDUP((Date_Day_part-7)/7)-ROUNDDOWN((Date_Day_part-7)/7)=0, ROUNDDOWN((Date_Day_part-7)/7)+1, ROUNDDOWN((Date_Day_part-7)/7)+2)),
       IF(First_day_of_week_of_the_month="Monday", IF(Date_Day_part<=6, 1, IF(ROUNDUP((Date_Day_part-6)/7)-ROUNDDOWN((Date_Day_part-6)/7)=0, ROUNDDOWN((Date_Day_part-6)/7)+1, ROUNDDOWN((Date_Day_part-6)/7)+2)),
         IF(First_day_of_week_of_the_month="Tuesday", IF(Date_Day_part<=5, 1, IF(ROUNDUP((Date_Day_part-5)/7)-ROUNDDOWN((Date_Day_part-5)/7)=0, ROUNDDOWN((Date_Day_part-5)/7)+1, ROUNDDOWN((Date_Day_part-5)/7)+2)),
           IF(First_day_of_week_of_the_month="Wednesday", IF(Date_Day_part<=4, 1, IF(ROUNDUP((Date_Day_part-4)/7)-ROUNDDOWN((Date_Day_part-4)/7)=0, ROUNDDOWN((Date_Day_part-4)/7)+1, ROUNDDOWN((Date_Day_part-4)/7)+2)),
             IF(First_day_of_week_of_the_month="Thursday", IF(Date_Day_part<=3, 1, IF(ROUNDUP((Date_Day_part-3)/7)-ROUNDDOWN((Date_Day_part-3)/7)=0, ROUNDDOWN((Date_Day_part-3)/7)+1, ROUNDDOWN((Date_Day_part-3)/7)+2)),
               IF(First_day_of_week_of_the_month="Friday", IF(Date_Day_part<=2, 1, IF(ROUNDUP((Date_Day_part-2)/7)-ROUNDDOWN((Date_Day_part-2)/7)=0, ROUNDDOWN((Date_Day_part-2)/7)+1, ROUNDDOWN((Date_Day_part-2)/7)+2)),
                 IF(First_day_of_week_of_the_month="Saturday", IF(Date_Day_part<=1, 1, IF(ROUNDUP((Date_Day_part-1)/7)-ROUNDDOWN((Date_Day_part-1)/7)=0, ROUNDDOWN((Date_Day_part-1)/7)+1, ROUNDDOWN((Date_Day_part-1)/7)+2)), "N/A"
                 )
               )
             )
           )
         )
       )
     )

  If you want to use Monday as the start of a week, replace "Sunday" in the following part of the formula with "Monday": First_day_of_week_of_the_month="Sunday" Similarly, replace each of the days of the week in this formula with the one that follows.
  For more details on this formula, refer to Explanation of the formula for field (5).

Based on the monthly week number calculated in field (5), this field displays the monthly week number for the date entered in field (1) in the following format: Week N of MMMM YYYY

• Field name: Monthly week number (full format)

• Field code: Monthly_week_number_full

• Formula: Select Calculate automatically and set the following formula.

  Copy

  The content has been copied.

  "Week "&Monthly_week_number&" of "&DATE_FORMAT(Date, "MMMM YYYY", "Asia/Tokyo")

Based on the first day-of-week of the month calculated in field (4), field (5) calculates the monthly week number for the date using the day part displayed in field (2).

Since the formula for field (5) uses the IF function, the calculation flow splits into several branches depending on which condition is met.

The number of days in the first week of the month is determined based on the first day-of-week of the month.
As an example, the formula explained in this article assumes that the start of a week is Sunday.

• Example: If you use Sunday as the start of a week
  • If the first day-of-week of the month is Monday, the first week of the month consists of 6 days.
  • If the first day-of-week of the month is Tuesday, the first week of the month consists of 5 days.

By calculating whether or not the number (day part) displayed in field (2) is larger than the number of days in the first week of the month, field (5) determines whether the given date is in the first week or in the second or subsequent weeks.

• If the day part is equal to or smaller than the number of days in the first week of the month
  The date is recognized to be in the first week of the month.
  Field (5) will display "1", which represents the first week.
• If the day part is larger than the number of days in the first week of the month
  The date is recognized to be in the second or subsequent weeks of the month.
  In this case, the calculation continues. The calculation that follows will identify which specific week the date is in.

If the day part is larger than the number of days in the first week of the month, the formula performs the calculation to identify which specific week the date is in.

First, the number of days that have passed since the last day of the first week is calculated by subtracting the number of days in the first week from the number (day part) displayed in field (2).
Then, the calculation result is divided by 7, which is the number of days in a week.

The week number for the date is the quotient plus 1 (week), which represents the first week of the month.
Field (5) displays the value obtained by adding 1 to the quotient.

Example: If the first day of the month is Monday and the date entered is the 13th
If the first day of the month is Monday, the first week of the month consists of 6 days.
13 (the day part of the given date) minus 6 (the number of days in the first week of the month) is 7. This indicates that 7 days have passed since the last day of the first week.
7 divided by 7 (the number of days in a week) is 1 (week) with no remainder.
Then, 1 (week), which represents the first week of the month, is added to the quotient of the above calculation, 1 (week). The outcome is 2 (weeks), which tells you that the given date is in the second week of the month.

The week number for the date is the quotient plus 2 (weeks), which represents the first week of the month plus one more week for covering the days left over.
Field (5) displays the value obtained by adding 2 to the quotient.

Example: If the first day of the month is Monday and the date entered is the 15th
If the first day of the month is Monday, the first week of the month consists of 6 days.
15 (the day part of the given date) minus 6 (the number of days in the first week of the month) is 9. This indicates that 9 days have passed since the last day of the first week.
9 divided by 7 (the number of days in a week) is 1 (week) with a remainder of 2 (days).
Then, 2 (weeks), which represents the first week of the month plus one more week for covering the days left over, is added to the quotient of the above calculation, 1 (week). The outcome is 3 (weeks), which tells you that the given date is in the third week of the month.

To aggregate record data by monthly week number, specify field (6) (the field that displays the monthly week number for a given date), which you have set up in the sections above, in the Group by section in the Graph settings.

• Example of the Graph settings

• Example aggregate result

Also, if a "Date" field is placed on your app form, you can use the Chart feature to aggregate record data by yearly week number.
This can be done by specifying the "Date" field and by the week in the Group by section in the Graph settings.
For instance, if the"Date" field value is "Mar 30, 2022", the record data is aggregated as "2022 13" as the date is in the 13th week of 2022.

You can download an app template with preconfigured formulas and Graph settings from the link below.

App template "what_week_en.zip"

Click the link to download the template file (zip) and import it into Kintone without unzipping it.