Conceptually challenged…

6.42K 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, No problem. I am actually going to revisit your model because I think there is some efficiencies that can be gain. You might want to consider combining the Fuel Week and the Pay Week date matrices into one and then link your dates catagory. This will help a lot in making some eloquent formulas. This will also allow you to line up your pay week and fuel week and possibly eliminate the need for the lookup formula. Like I said, I will play around with it some to see it that can be done.


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

Latest Questions