Displaying the Last Day of the Month Based on the Date Value (Taking into Account Leap Years)
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
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
Setting formulas
Set formulas for the "Calculated" fields and the "Text" fields.
-
The formula for the "Year" field:
DATE_FORMAT(Date, "YYYY", "Etc/GMT")
-
The formula for the "Month" field:
DATE_FORMAT(Date, "M", "Etc/GMT")
-
The formula for the "Leap year check" field:
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:
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