Article Number：010211

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.

○Preparation

Place the following fields in the form of the app settings and set field names, field codes, and formulas for each.

• Date field
  ・Field Name/Field Code: Date
  
  
• Calculated field
  ・Field Name/Field Code:Year
  ・Formula：

  DATE_FORMAT(Date, "YYYY", "system")

  ・Show As: Number (1000)
  
  
• Calculated field
  ・Field Name/Field Code:Month
  ・Formula：

  DATE_FORMAT(Date, "M", "system")

  ・Show As: Number (1000)
  
  
• Text field
  ・Field Name/Field Code:Leap_year_check
  ・Formula：

  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")

• Text field
  ・Field Name/Field Code:Last_day_of_the_month
  ・Formula：

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

※ You can make the "Year," "Month," and "Leap_year_check" fields hidden by setting field access permissions.
    Even if field access permissions are set, the "Last Day of the Month" field will be calculated correctly. 
    Configuring Permissions for Fields

○Explanation of the Formula

• 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

For reference, we have attached the template file "leap_year.zip" (Japanese only) with the formula configured.
Download the template file, and without unzipping it, import it into kintone.
Creating Apps from Template Files