Environment
- OS: Windows 10 Pro (Version: 20H2)
- Excel Version: 2108
Background
When creating a calendar in Excel, I wanted to configure settings to display or hide the date “31” depending on the month. Specifically, I wanted to create a calendar where “31” is hidden for months that only have 30 days (or fewer) and displayed for months that have 31 days. I learned how to achieve this mechanism and will share the method.
Implementation Details
Using a combination of the IF and OR functions
The functions used this time are the IF function and the OR function. It is assumed that the month number (1 to 12) is entered in cell J1.
The formula used is as follows:
=IF(OR(J1=1,J1=3,J1=5,J1=7,J1=8,J1=10,J1=12),31,"")
Explanation of Functions
IF Function
- Format:
IF(logical_test, value_if_true, value_if_false)
In this case, the OR function is used for the logical test. If the condition is True, “31” is displayed. If the condition is False, the cell is left blank ("").
OR Function
- Format:
OR(logical1, logical2, logical3, ...)
If any one of the multiple conditions is true, the entire function is determined to be true. Here, the condition checks whether the month corresponds to “months with 31 days,” which are January, March, May, July, August, October, and December.
Specifically, the formula summarizes the following conditions:
J1=1, J1=3, J1=5, J1=7, J1=8, J1=10, J1=12
As a result, “31” is displayed for applicable months, and it remains blank for other months.
Summary
In this article, I covered how to “display 31 only for months where the 31st exists” in Excel. By applying similar logic, you can also handle the 28th or 29th of February. This is a very useful method for those who want to create flexible date displays in calendars, so please try using it.
