Conceptually challenged…

6.66K viewsFormulas and Functions

How do I include income and expense items that are credited/debited on different cycles? An example file is attached.

Weekly Settlements are completed each Friday.
Weekly Settlements are paid via direct deposit each Monday.

Weekly Settlements include:

Payment for Work Orders for which associated documents have been recieved by mail by the previous Tuesday (basically anything mailed by the Friday before that Tuesday). A Work Order may or may not span 2 seperate “Fuel Weeks” (see below). Work Orders take from several hours to several days to complete.

Expenses deducted include:
Fuel bought with Fuel Card during the previous “Fuel Week”, which ends on Saturday (regardless of associated Work Order #), and is deducted from the next Weekly Settlement (which is completed the following Friday).

I would like to enter individual Fuel purchases into a “Fuel Purchases” matrix, and have the “Fuel” item under each “Pay Wk” column in the “Weekly Expenses” matrix automatically show ONLY the total cost of all Fuel that was purchased between Sun through Sat of the previous “Fuel Week”.

Anyone have any ideas? Am I going about this the wrong way?


quasimod, I took a look at your model. I added the Fweek number to the pay week model.

In the Fuel Purchases matrix, you can change the fuel week item to “lookup” the fuel week number based on the the fueling date (which I did in the model but you should test and debug as necessary). I would then use that information to create a sumif in the Weekly Expenses matrix.

You will probably want to link you Pay Weeks Begin/End Dates 07 Pay Weeks catagory to your Weekly Expenses Pay Weeks catagory. This should help on your sumif formula above.

I hope this helps and if you need anymore help, just let me know.


You are viewing 1 out of 11 answers, click here to view all answers.

Latest Questions