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.

Image of the created form

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:

  • 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
    Related Information: Japanese Laws Related to Leap Years (This is not our web page.)

• 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