Filtering Data to Monthly report from a date range sheet.

[JTheHen]

I'm trying to create a monthly report using multiple variables. It's for tools borrowed for Jobs that I bill monthly.
I have the fallowing fields:

Date In Date Out Asset # Serial # BC# Equipment Description Branch Job # Job Name # of Weeks $ Per Week Sub total

I some of the dates In vs. dates out could be months or years away from each other.

I want to extract 1 month at a time. The fields in bold would be fixed.
But if they only used it for 3 weeks that month on that job I have to record that. To come up with a $ to bill the Job.

So it would look like this:

October (Drop down)

Branch (Drop down)

Asset # Serial # BC# Equipment Description Job # Job Name # of Weeks $ Per Week Total to bill (for that Month)


Tried filtering (but that won't extract just the one moth I want) , Pivot Table (but that just give me a count if the records in that field).

Any suggestions?

 

[pbornemeier]

Try advanced filtering with the Date In / Date Out columns

To select all that were in the month of November 2015

Add a new Column at the end of your data called "Days In Use"
Add 3 blank rows above your data
Copy the Headers from Row4 to Row2
In A1, enter the first day of the month that you want to get data for
Copy and paste the following formulas to the indicated cells:

Cell    Formula
B1      =DATE(YEAR(A1),MONTH(A1)+1,0)
A3      ="<=" & DATE(YEAR(A1),MONTH(A1)+1,0)
B3      =">="&DATE(YEAR(A1),MONTH(A1),1)

Copy this formula to Row 5 of the column that contains the "Days In Use" header

Cell    Formula
?5      =IF(B5>$B$1,$B$1,B5)-IF(A5<$A$1,$A$1,A5)+1

Copy the "Days In Use" formula down to the last row of your data

Leave all formulas as formulas, DO NOT convert to text.

Click Data | Sort & Filter | Advanced
Select the List Range and set it from A4 to the bottom and right of your data
Select the Criteria Range and set it to$A$2:$B$3
Click OK
Only the rows for tools you held in the month in A1 will be shown.

 

[JTheHen]

Thanks pbornemeier ,
I wish I could post a screen shot. It would be obvious to most what the answer is.
I'm now getting negative numbers in the "days of use" column. And as soon as I sort I lose the headers.
But you got me one step closer. I do appreciate that.

 

[pbornemeier]

Select A4 to the end of your data for the sort before sorting..
Leave the data entry (row 1) advanced filter stuff (rows 2:3) out of the sort.

Some of the "days of use" values will be negative until the data is filtered by putting a date in A1 and applying the advanced filter as described.
Once the filter is applied the only rows where the dates overlap the month in A1 should be shown and the days of use column for those rows will have a number from 1 to 31 showing how many days the tools in each row were checked out to you.

 

[JTheHen]

I fallowed the instructions to exactly.
But it hides all the data rows. Even when I change A1 date it doesn't seem to help.
The "days in use" data does seem to update. But the filter hides everything.

Maybe I could use vlookup on another sheet with Data validation to sort by branch & Positive "Days in Use" #'s?
What do you think?

Might make it more user friendly for the accountants that are going to use it?

Can't say Thanks enough for for help.

 

[pbornemeier]

The advanced filter must be reapplied if the date in A1 is changed.
It works for me and I would like to resolve the difference.
If you format A1, A3, B1, B3 with the same date format does A1=A3 and B1=B3 ?
Please use Excel Jeanie (see link in my sig) to post the first 10 or so lines of your modified worksheet.

 

[JTheHen]

Unfortunately I don't have admin rights on my work computer. So I can't install Excel Jeanie.
I'm trying to find another way.