How to calculate the date N months later considering leap years

Article Number：01055

By setting the DATE_FORMAT function and the IF function, you can calculate the date N months later considering leap years.

Here, we will explain how to calculate the date one month later considering 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)
Calculated Field
・Field Name/Field Code: Day
・Formula: DATE_FORMAT(Date, "d", "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 (Single Line) Field
・Field Name/Field Code: Next Month Display
・Formula: IF(date="","",
IF(Month=12,Year+1&" Year 1",Year&" Year "&Month+1)&" Month "&
IF(AND(Month=1,Day>=29,Leap_year_check="Leap year"),"29",
IF(AND(Month=1,Day>=29,Leap_year_check="Common year"),"28",
IF(AND(OR(Month=3,Month=5,Month=8,Month=10),Day=31),"30",Day)))
&" Day")

※The "Year", "Month", "Day", and "Leap year check" fields can be hidden by setting field permissions.
Even if you set field permissions, the "Next Month Display" 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:
Reference: Laws and Regulations Concerning Leap Years（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
In the "Next Month Display" field, the date to be displayed is calculated based on the value of the "Date" field.
・If the last day of the month is specified, the following behavior occurs:
- If that day exists: The same day of the next month is displayed.
- If that day does not exist: The last day of the next month is displayed.

・For January 29th/30th/31st, "Ordinary Year" displays February 28th, and "Leap Year" displays February 29th.
IF function: Change the value to display depending on a condition

Search

How can I help?