Hi Richard,

I have a spreadsheet which lists my Bills, Pay dates and cash on hand based on pay dates. I wanted to know if there a formula or way to auto populate the cash on hand based on the dates my bills are due and the cash i have available after my pay dates. For Example Column "A" is bills. Column "B" is pay dates, and "C" is cash. I want column "C" (cash on hand) to automatically reflect any expenses that occur between the current pay date and the next bi-weekly pay date. I can physically create a formula with this information based on the cells I need but I was wondering if there was a way I could do this based on the "Bill" "Due Date".

Right now I have the pay date set up as (pay date cell+14) since I'm paid bi-weekly. The range between my "pay dates" change but the "due date" does not. Can excel discern my cash on hand based on which bills occur during ever changing "pay dates"? Not sure if I'm over or under explaining my dilemma.  Any advice you could offer is greatly appreciated.
 

---

Answer from Richard Rost:

You just want to put a value in column C if the date in column B is in the past? That's just an IF function:

=IF(B1
This says if B1 is less than today (in the past) then use the value in A1, otherwise use zero.

You can use the IF function to have Excel place a value in a cell based on one or more conditions or values in other cells.

See this FREE tutorial on my web site for more information on how to use the IF function:

http://www.599cd.com/tips/excel/misc/if-function-sales-tax.asp?key=BlFAlEx