How to get the difference between two dates based on an item (truck) number

Question

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

Answer 1

Would a pivot table work for you? Here's what you might try... In Excel 2007, first split your comma-separated data by using the "Text-to-Columns" item on the "Data" menu.

Next, "Insert" a pivot table, and set up using the as shown below. Put the "Truck" field in the "Report Filter" section. Put the "Date" and "Last" fields in the "Row Labels" section. Put the "Remaining" field in the "Values" section and choose "Sum". Note that you can sort the "Date" field in the pivot table in descending order, which of course brings the most recent use for the particular truck to the top of the list.

If you need to see all the trucks at once, you can move the "Truck" field out of the "Report Filter" section to the "Row Labels" section.