Displaying the Day of the Week Based on the Date Value
By using a formula, you can display the day of the week for date values.
Preparations
- Place a "Date" or "Date and time" field on the app form.
Specify the field code of the field ("Date" for a "Date" field, or "Date_and_Time" for a "Date and time" field). - Place a "Text" field on the form. The field code setting is optional.
- In the Misc Settings, set a value that is greater than or equal to 1 for Number of Decimal Places to Round.
For details, refer to the following pages.
Overview of Field Codes
Setting the Significant Digits of Numbers and the Rounding Method
Formula
On the settings dialog of the "Text" field, select Calculate automatically and enter one of the following formulas.
The formula to be set for the "Text" field differs depending on whether the other field placed on the app form is a "Date" field or "Date and time" field.
Formula to use when a "Date" field is placed on the app form
IF( ( ( (Date / 60 / 60 / 24) - 0) / 7 ) - ROUNDUP(( ( (Date / 60 / 60 / 24) - 0) / 7 ) , 0) = 0 , "Thursday",
IF( ( ( (Date / 60 / 60 / 24) - 1) / 7 ) - ROUNDUP(( ( (Date / 60 / 60 / 24) - 1) / 7 ) , 0) = 0 , "Friday",
IF( ( ( (Date / 60 / 60 / 24) - 2) / 7 ) - ROUNDUP(( ( (Date / 60 / 60 / 24) - 2) / 7 ) , 0) = 0 , "Saturday",
IF( ( ( (Date / 60 / 60 / 24) - 3) / 7 ) - ROUNDUP(( ( (Date / 60 / 60 / 24) - 3) / 7 ) , 0) = 0 , "Sunday",
IF( ( ( (Date / 60 / 60 / 24) - 4) / 7 ) - ROUNDUP(( ( (Date / 60 / 60 / 24) - 4) / 7 ) , 0) = 0 , "Monday",
IF( ( ( (Date / 60 / 60 / 24) - 5) / 7 ) - ROUNDUP(( ( (Date / 60 / 60 / 24) - 5) / 7 ) , 0) = 0 , "Tuesday",
IF( ( ( (Date / 60 / 60 / 24) - 6) / 7 ) - ROUNDUP(( ( (Date / 60 / 60 / 24) - 6) / 7 ) , 0) = 0 , "Wednesday","N/A"
)
)
)
)
)
)
)
Formula to use when a "Date and time" field is placed on the app form
IF( ( ( (ROUNDDOWN((Date_and_Time+(60 * 60 * 9))/ 60 / 60 / 24) ) - 0) / 7 ) - ROUNDUP(( ( (ROUNDDOWN((Date_and_Time+(60 * 60 * 9))/ 60 / 60 / 24)) - 0) / 7 ) , 0) = 0 , "Thursday",
IF( ( ( (ROUNDDOWN((Date_and_Time+(60 * 60 * 9))/ 60 / 60 / 24) ) - 1) / 7 ) - ROUNDUP(( ( (ROUNDDOWN((Date_and_Time+(60 * 60 * 9))/ 60 / 60 / 24)) - 1) / 7 ) , 0) = 0 , "Friday",
IF( ( ( (ROUNDDOWN((Date_and_Time+(60 * 60 * 9))/ 60 / 60 / 24) ) - 2) / 7 ) - ROUNDUP(( ( (ROUNDDOWN((Date_and_Time+(60 * 60 * 9))/ 60 / 60 / 24)) - 2) / 7 ) , 0) = 0 , "Saturday",
IF( ( ( (ROUNDDOWN((Date_and_Time+(60 * 60 * 9))/ 60 / 60 / 24) ) - 3) / 7 ) - ROUNDUP(( ( (ROUNDDOWN((Date_and_Time+(60 * 60 * 9))/ 60 / 60 / 24)) - 3) / 7 ) , 0) = 0 , "Sunday",
IF( ( ( (ROUNDDOWN((Date_and_Time+(60 * 60 * 9))/ 60 / 60 / 24) ) - 4) / 7 ) - ROUNDUP(( ( (ROUNDDOWN((Date_and_Time+(60 * 60 * 9))/ 60 / 60 / 24)) - 4) / 7 ) , 0) = 0 , "Monday",
IF( ( ( (ROUNDDOWN((Date_and_Time+(60 * 60 * 9))/ 60 / 60 / 24) ) - 5) / 7 ) - ROUNDUP(( ( (ROUNDDOWN((Date_and_Time+(60 * 60 * 9))/ 60 / 60 / 24)) - 5) / 7 ) , 0) = 0 , "Tuesday",
IF( ( ( (ROUNDDOWN((Date_and_Time+(60 * 60 * 9))/ 60 / 60 / 24) ) - 6) / 7 ) - ROUNDUP(( ( (ROUNDDOWN((Date_and_Time+(60 * 60 * 9))/ 60 / 60 / 24)) - 6) / 7 ) , 0) = 0 , "Wednesday","N/A"
)
)
)
)
)
)
)
Formula explanation
Formulas in kintone treat the values in "Date" and "Date and time" fields as Unix time, which is the number of seconds elapsed since 1970-1-1 00:00 UTC (Coordinated Universal Time).
For details, refer to the following page.
How Values in Date, Time, and Date and Time Fields Are Treated
In the formulas on this page, the date value entered in a field is converted to the number of days elapsed since the Unix epoch and then calculated. The day of the week that corresponds to a date value is displayed using the concept of dividing the number of days elapsed since the Unix epoch by 7 (the number of days in a week), and then determining the day of the week based on the remainder.
- Examples:
- If you enter April 1, 2021:
The number of days that have elapsed since Thursday, January 1, 1970 is 18,718.
18,718 divided by 7 is 2,674 with remainder 0.
This means that April 1, 2021 is exactly 2,674 weeks after Thursday, January 1, 1970. Because the date is 0 days after Thursday, the day of the week must be Thursday.
In other words, the day of the week is always Thursday when the number of days that have elapsed since January 1, 1970 is divided by 7 and the remainder is 0. - If you enter April 2, 2021:
The number of days that have elapsed since Thursday, January 1, 1970 is 18,719.
18,719 divided by 7 is 2,674 with remainder 1.
This means that April 2, 2021 is 2,674 weeks and one day after Thursday, January 1, 1970. Because the date is one day after Thursday, the day of the week must be Friday.
In other words, the day of the week is always Friday when the number of days that have elapsed since January 1, 1970 is divided by 7 and the remainder is 1.
- If you enter April 1, 2021:
In the examples above, the remainder returned after dividing the number of days elapsed since the Unix epoch by 7 is used to find the day of the week as follows.
A) When the number of elapsed days is divided by 7 and it returns remainder 0: Thursday
B) When the number of elapsed days is divided by 7 and it returns remainder 1: Friday
C) When the number of elapsed days is divided by 7 and it returns remainder 2: Saturday
D) When the number of elapsed days is divided by 7 and it returns remainder 3: Sunday
E) When the number of elapsed days is divided by 7 and it returns remainder 4: Monday
F) When the number of elapsed days is divided by 7 and it returns remainder 5: Tuesday
G) When the number of elapsed days is divided by 7 and it returns remainder 6: Wednesday
However, the calculation features of kintone cannot be used to find remainders.
For this reason, instead of determining the day of the week directly from the remainder, the day of the week is determined by subtracting the remainder value from the number of days elapsed and calculating a value that is evenly divisible by 7.
To calculate a value that is evenly divisible by 7, the remainder value is returned as a decimal number, which is then rounded up to the nearest whole number using the ROUNDUP function.
When the difference of subtracting [(the number of elapsed days - X) divided by 7] from [the quotient of dividing (the number of elapsed days - X) by 7 rounded to a whole number using the ROUNDUP function] is 0, it is determined that a value is evenly divisible by 7 (or, in other words, has a remainder of 0). The day of the week is then determined as follows.
A) When the number of elapsed days minus 0 is divided by 7 and it returns remainder 0: Thursday
B) When the number of elapsed days minus 1 is divided by 7 and it returns remainder 0: Friday
C) When the number of elapsed days minus 2 is divided by 7 and it returns remainder 0: Saturday
D) When the number of elapsed days minus 3 is divided by 7 and it returns remainder 0: Sunday
E) When the number of elapsed days minus 4 is divided by 7 and it returns remainder 0: Monday
F) When the number of elapsed days minus 5 is divided by 7 and it returns remainder 0: Tuesday
G) When the number of elapsed days minus 6 is divided by 7 and it returns remainder 0: Wednesday
For information on the ROUNDUP function, refer to the following page.
ROUND, ROUNDUP, and ROUNDDOWN Functions: Round Off, Round Up, and Round Down Numeric Values