Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
All articles Miscellaneous Fixed Value Every N Months

Fixed Value Every N Months

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.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Solution examples
Could you help me by explaining the IF function?
Solved by G. L. in 24 mins
I have a question about Conditional Formatting. =IF('Sheet1'!A1="X",IF('Sheet1'!B1="Y", TRUE, FALSE),FALSE) the set the cell background to red. I would like to make it so that if Sheet1'!B1="Z" I set the back to blue. How can I achieve this.
Solved by B. F. in 39 mins
I need to find out if the 2018 yearly sales goals were met if the yearly sales were $25,000 or more using an IF logical function and to set the formula to return a value of YES if met, and NO if not
Solved by A. A. in 18 mins
I filtered data in column A (it is labeled ID #) of a data set. On my next worksheet, column A is also the ID #, but it is a different data set. I want to filter the ID #'s the same for the two sheets, but how do I do that being that I am working with two different sets of data?
Solved by V. H. in 22 mins
i want to create a very simple if function in a cell which is, if one cell is less than 1250, then the automatic value in the current cell is 250, dead easy, for someone who knows! Thank you.
Solved by A. H. in 14 mins

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

Did this post not answer your question?
Get a solution from connecting with the expert

Another blog reader asked this question today on Excelchat:

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc