0
I'm trying to set up a database for a co-worker to track information based on truck number, amount disposed, etc. Sometimes trucks are left with garbage at then end of the day. That truck may not be used for a number of days. I want to make a function that would find the last time that truck was used, and then count the days between. Then I can use that number to identify the date and pull the weight that was remaining on that particular truck.
Below is a small part of the database. "Last" is the number of days that has passed between the use of the truck. "Remainder" will pull the recorded "Remaining" value for that particular truck based on the last time it was used.
Date, Truck, Last, Type, Remainder, Ttl_Dump, Remaining, Collected,
03/09/12, 843, 1, Garbage, 14460, 38260, 8040, 31840,
03/10/12, 134, 1, Garbage, 6500, 6500, 0,
03/10/12, 840, 2, Garbage, 0, 10040, 0, 10040,
03/10/12, 843, 1, Garbage, 8040, 8040, 7420, 7420,
03/12/12, 137, 10, Garbage, 4000, 4000, 0, 0,
03/13/12, 842, 4, Garbage, 0, 13600, 4340, 17940
HAHA. Sorry, copied and pasted from an excel. I can't use HTML tables so I put the commas in just in case someone wanted to import it into excel. – Demian Carpenter – 2012-05-25T19:02:18.557
The thing is I don't want this guy to have to do that. I would rather it just calculate the right date used in order to pull the "remaining" number from the correct cell. I can't post images either, so I can't show you everything. I would use a pivot table, but the person I'm doing this for doesn't know that much about excel. You should see what he was doing. I did sort and used =day360(start,end,method), copied the results into notepad, copied it back into excel, then sorted again by date. I'd rather he skip that process to avoid potential errors. – Demian Carpenter – 2012-05-25T19:07:39.750
I also created another workbook that has different tabs he can use to report totals. Daily, monthly, yearly, truck usage, employee info, etc. – Demian Carpenter – 2012-05-25T19:13:20.057