I have a weekly cashflow spreadsheet. The columns are the allocated weekly expenses, the column headings are a date = "week ending". I am using the "=IF(AND" comparator to determine if a task (expense) occurs during that particular week. I am using the "<=" equation to test if the task end date is less than or equal to the week ending date. If the task end date is the same as the week ending date, the formula doesn't work. It works for all other dates. Here is my formula:
= IF(AND(V$19>=$E85,$D85<U$19),DAYS($E85,U$19)*$O85,)
V$19: Week-ending Date of current week
U$19: Week-ending Date of previous week
$D85: Start date of task
$E85: End date of task
DAYS($E85,U$19): the number of days in the current week the task is performed.
$O85: an Expense constant
Please note, this equation looks only at tasks that started before the week, but finish during the week. I have other equations for tasks that start and finish within the week etc.
Bookmarks