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

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.

enter image description here

  • 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 May 25 '12 at 19:02
  • 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 May 25 '12 at 19:07
  • 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 May 25 '12 at 19:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.