By placing on the app form a Date field and a field with a formula for determining if a year is a leap year, you can display the last days of months taking into account leap years.

How the completed form will look

Place the following fields in the form and set their field names and field codes.

• Date field
  Field name and field code: "Date"
• Calculated field
  Field name and field code: "Year"
• Calculated field
  Field name and field code: "Month"
• Text field
  Field name and field code: "Leap year check" and "Leap_year_check" respectively
• Text field
  Field name and field code: "Last day of the month" and "Last_day_of_the_month" respectively

Set formulas for the Calculated fields and the Text fields.

• The formula for the "Year" field:

  Copy

  The content has been copied.

  DATE_FORMAT(Date, "YYYY", "Etc/GMT")

• The formula for the "Month" field:

  Copy

  The content has been copied.

  DATE_FORMAT(Date, "M", "Etc/GMT")

• The formula for the "Leap year check" field:

  Copy

  The content has been copied.

  IF(OR(Year/4-ROUNDDOWN(Year/4)!=0,AND(Year/100-ROUNDDOWN(Year/100)=0,Year/400-ROUNDDOWN(Year/400)!=0)),"Common year","Leap year")

• The formula for the "Last day of the month" field:

  Copy

  The content has been copied.

  IF(OR(Month=4,Month=6,Month=9,Month=11),DATE_FORMAT(Date, "YYYY-M-30", "Etc/GMT"),IF(AND(Month=2,Leap_year_check="Leap year"),DATE_FORMAT(Date, "YYYY-M-29", "Etc/GMT"),IF(AND(Month=2,Leap_year_check="Common year"),DATE_FORMAT(Date, "YYYY-M-28", "Etc/GMT"),DATE_FORMAT(Date, "YYYY-M-31", "Etc/GMT"))))

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

App template: leap_year.zip (Only available in Japanese)

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

• The "Leap year check" field displays either "Common year" or "Leap year" depending on the "Year" field value.
  For your reference, whether a year is a "Common year" or "Leap year" is calculated based on the following definition of leap years stipulated in the Japanese laws.
  For details, refer to the following page.
  Laws and regulations concerning leap years (This is an external site that is only available in Japanese)
  • A year that is evenly divisible by four is regarded as a leap year
  • A year that is evenly divisible by 100 but not by 400 is regarded as a common year
• The "Last day of the month" field uses IF functions to express the following four types of the last day of a month:
  • The last day of April, June, September, and November is the 30th
  • The last day of February in a leap year is the 29th
  • The last day of February in a common year is the 28th
  • The last day of the remaining months (January, March, May, July, August, October, and December) is the 31st
    IF function: Change the value to display depending on a condition