Excel allows a user to write a fixed value every n months in a defined column using several functions: **IF, MOD **and **DATEDIF**. This step by step tutorial will assist all levels of Excel users to assign a certain value every n months from a defined starting date.

*Figure 1. Get fixed value every n months*

**Syntax of the IF Formula**

**=IF(logical_test, value_if_true, value_if_false)**

The parameters of the IF function are:

**logical_test**– a logical expression that we want to check**value_if_true**– a value which the function returns if a logical_test is TRUE**value_if_false**– a value which the function returns if a logical_test is FALSE.

**Syntax of the MOD Formula**

**=MOD(number,divisor)**

The parameter of the MOD function is:

**number – a number for which we want to find a remainder after the division**

- divisor – a number that will be used as a divisor

**Syntax of the DATEDIF Formula**

**=DATEDIF(start_date, end_date, unit)**

The parameters of the DATEDIF function are:

**start_date**– a date from which we want to count the difference

**end_date -a date to which we want to count the difference**

- unit – time unit in which we want to present the difference between the two dates (days, months, years)

**Setting up Our Data for the Formula**

Let’s look at the structure of the data we will use. In column B (“Date”) we have dates, while in column C (“Values”) we will place the formula that assigns the fixed value every n months. In column F is data for the calculation. The “n months” is every other month for which the fixed value should be displayed. The “value” is a $100 value that will be displayed every other month while the “start date” is a date from which will be calculated every n month, 1-Jan-19.

* Figure 2. Data that we will use in the example*

**Assign the Fixed Value Every Other Month**

The idea is to write $100 in column C every other month starting from 1-Jan-19. For this purpose, we will use IF, DATEDIF and MOD functions. In order to make the formula more clear we will create three named ranges, **n_months** for the cell F2, **value** for the cell F3 and the **start_date** for the cell F4.

To create a named range we should follow the steps:

- Select the cell range that should be named
- Click on the name box in Excel
- Write the name for the cell range and press enter

* Figure 3. Creating a named range n_months*

* Figure 4. Creating a named range value for the fixed value $100*

* Figure 5. Creating a named range start_date for the example*

The formula looks like:

**=IF(B3>=start_date,(MOD(DATEDIF(start_date,B3,"m")+n_months,n_months)=0)*value,0)**

The **logical_test **in the IF function is checking if the date in the cell B3 is higher than the start_date, B3>=start_date. The parameter **value_if_true** is formula ** (MOD(DATEDIF(start_date,B3,"m")+n_months,n_months)=0)*value**, while the

**value_if_false**is 0.

In MOD function **number** is the formula ** DATEDIF(start_date,B3,"m")+n_months**, while the parameter

**divisor**is the named range n_months. In DATEDIF function

**strat_date**is a named range start_date, while the

**end_date**is the cell B3. The argument

**unit**is an “m” since we want to get the difference in the months.

To apply the formula, we need to follow these steps:

- Select cell B3 and click on it
- Insert the formula:
`=IF(B3>=start_date,(MOD(DATEDIF(start_date,B3,"m")+n_months,n_months)=0)*value,0)`

- Press enter
- Drag the formula down to the other cells in the column by clicking and dragging the little “+” icon at the bottom-right of the cell.

* Figure 6. Assign fixed value every other month with IF, MOD and DATEDIF functions*

The DATEDIF function calculates the difference between the date in the cell B3 and the start date. The result is 0 months because the dates are the same. The MOD function gets the remainder of the DATEDIF function result increased by 2 and with the divisor 2. The output of the MOD formula is 0 because there is no remainder.

The IF function is checking if the date in the cell B3 is greater or equal to the start date. The condition is met and IF formula looks like IF(TRUE,(0=0)*value,0). Formula part 0=0 is checking if there is no a remainder in the MOD function. The result is TRUE and the formula looks like: ** IF(TRUE,TRUE*100,0)**. The TRUE is interpreted as number 1 and formula output is $100.

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

## Leave a Comment