Calculate Days in Month Book is checked out

[Will85]

Sheet 1 column A stores Check-out dates, column B stores Return Dates
If the last entry in Column A does not have a corresponding entry in Column B, the book is still checked out

A book can be checked in and out an unknown number of times. A book cannot be checked out and then returned (or vice versa) on the same day, and both the day a book is checked out and the day it is returned are included in the days the book was checked out.

Sheet 2 A1:L1 has the 12 months going across Jan - Dec of a particular year, say 2019

Sheet 2 A2:L2 needs to sum the number of days in that month that the book was checked out, based on the check-in and out days.

Sheet 3 A1 has the last day of a specific month for the report, as an example 6/30/2019. So my report would be as of 6/30/2019 and months Jan through June would calculate on Sheet 2. (If a book has no return date, then the max number of days checked out would be from the check out date, up to 6/30/2019). The cell in Sheet 3 A1 will always be the last day of the month the report is for.

For example, if sheet 1 had three entries A1= 12/15/2018 B1= 2/28/2019 and A2= 5/29/2019, then sheet 2 A2 = 31, B2 = 28, C2 = 0, D2 = 0, E2 = 3, F2 = 30, and zero for the rest of the months since the report is as of June.

 

[ranman256]

I use custom function: countDays(a2,a3)

function countDays(pvStartDate, pvEndDate)
 countDays = DateDiff("d",pvStartDate,  pvEndDate
end function

 

[Will85]

I use custom function: countDays(a2,a3)

function countDays(pvStartDate, pvEndDate)
countDays = DateDiff("d",pvStartDate,  pvEndDate
end function

I wanted to do this via built in excel formula in each month.