Exceljet

Quick, clean, and to the point

Expense begins on specific month

Excel formula: Expense begins on specific month
Generic formula 
=IF(start<=month,expense,0)
Explanation 

To start an expense on a specific month, you can use a formula based on the IF function. In the example shown, the formula in cell E5 (copied down and across) is:

=IF($D5<=E$4,$C5,0)

where the values in column D (start) and the range E4:J4 are valid Excel dates.

How this formula works

The first thing this formula does is check the date in column D against the date in the header (E4:J4).

=IF($D5<=E$4

Translated: if the date in column D is less than or equal to the date in row E.

Note these are mixed references. The column in $D5 is locked, and the row in E$4 is locked. This allows the formula to be copied across the table.

If the result of this test is TRUE, the IF function returns the expense from column C, otherwise IF returns zero (0).

=IF($D5<=E$4,$C5,0)

Again note the reference to $C5 is has the column locked, so the expense is always picked up from column C.

As the formula is copied across the table, expenses begin on the correct month. In months where the expense is not yet valid, the result is zero.

With boolean logic

Using boolean logic, the formula could be re-written like as below, for a simpler formula:

=$C5*($D5<=E$4)

Here the logical expression is used to "cancel out" expenses in months where they have not yet started.

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.