Calculating days checked out from a date range

Hello, all:

Our organization has a loaner pool of several different kinds of laptops and mi-fi devices that are available for check-out. Each full-cycle transaction (the device type, check-out date, return due date, and check-in date) is a row in an Excel spreadsheet. We need to calculate (and plot) the total number of laptops or mi-fi devices that are available so that we can know if we are maintaining the right number of devices in our loaner pool. We are not only interested in the current status of our loaner pool, but also the total number of devices that were in the checked-out or checked-in state per day (i.e., for each row) within a given range of dates in the past (for trending).

We have tried explicitly running columns for the date range of interest out to the right of the check-in dates for each transaction record, calculating “IN” or “OUT” for each date in the range, (as in the attached file), but that method quickly gets out of hand for date ranges of more than a couple of months. Please note that the logic is still not correct for calculating "IN" or "OUT" (I'm still working on it). The chart at the bottom of the data is a good representation of what we are trying to generate, but it was done using explicit columns.

Is there a better way to calculate, count and plot the status of each loaner pool item within the date range without explicitly listing every date as a column?

Any help you could give me would be greatly appreciated. Thanks in advance ...

Philip

Perfect!! Thank you very much, Kaper!!

Hi, Kaper: I've been away for a few days and I'm still digesting your solution. I have a follow-up question: Is there a way to calculate the days that are "checked out" within the data range that will allow me to do pivot charts on the columns (i.e., Seat Type, Seat Category, etc.) that are associated with the row data?