Displaying the Last Day of the Month Based on the Date Value (Taking into Account Leap Years)

Article Number:040765

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 Screenshot: Leap year calculation results

Preparation

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

Screenshot: All of the fields after they have been placed on the app form

Setting formulas

Set formulas for the "Calculated" fields and the "Text" fields.

  • The formula for the "Year" field:

    The content has been copied.
    DATE_FORMAT(Date, "YYYY", "Etc/GMT")
  • The formula for the "Month" field:

    The content has been copied.
    DATE_FORMAT(Date, "M", "Etc/GMT")
  • The formula for the "Leap year check" field:

    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:

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

Reference: App template

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.

Formula explanation

  • 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
      Reference: Laws and Regulations Concerning Leap Years (This is an external site that is only available in Japanese)
  • 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