Summary

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.

Generic formula

=IF(start<=month,expense,0)

Explanation 

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.

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.