Calculating days checked out from a date range

[psommers]

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