Summary

To repeat a fixed value every 3 months, you can use a formula based on the DATEDIF and MOD functions. In the example shown, the formula in C4, copied down, is:

=IF(B4>=start,(MOD(DATEDIF(start,B4,"m")+n,n)=0)*value,0)

where "start" is the named range F6, "n" is F4, and "value" is F5.

Generic formula

=IF(A1>=start,(MOD(DATEDIF(start,A1,"m")+n,n)=0)*value,0)

Explanation 

The first thing this formula does is check the date in column B against the start date:

=IF(B4>=start

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:

(MOD(DATEDIF(start,B4,"m")+n,n)=0)*value

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:

MOD(DATEDIF(start,B4,"m")+n,n)=0

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 Profile Picture

AuthorMicrosoft Most Valuable Professional Award

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.