Exceljet

Quick, clean, and to the point

Repeat fixed value every 3 months

Excel formula: Repeat fixed value every 3 months
Generic formula 
=IF(A1>=start,(MOD(DATEDIF(start,A1,"m")+n,n)=0)*value,0)
Explanation 

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.

How this formula works

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.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.