where “start” is the named range F6, “n” is F4, and “value” is F5. If the date is not greater than the start date, the formula returns zero. If the date is greater than or equal to the start date, the IF function runs this snippet: Inside MOD, the DATEDIF function is used to get the number of months between the start date and the date in B4. When the date in B4 equals the start date, DATEDIF returns zero. On the next month, DATEDIF returns 1, and so on. To this result, we add the value for the named range “n”, which is 3 in the example. This effectively starts the numbering pattern at 3 instead of zero. The MOD function is used to check each value, with n as the divisor: If the remainder is zero, we are working with a month that requires a value. Instead of nesting another IF function, we use boolean logic to multiply the result of the expression above by “value”. In months where there should be a value, MOD returns zero, the expression is TRUE, and value is returned. In other months, MOD returns a non-zero result, the expression is FALSE, and the value is forced to zero.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
