where the values in column D (start) and the range E4:J4 are valid Excel dates. 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). 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: Here the logical expression is used to “cancel out” expenses in months where they have not yet started.
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.
