Hide/Unhide Based on Several Date Range

**b2995** · 01-23-2012, 01:03 PM

Hi Everyone!

I have a workbook of timesheets on each employee. I would like to hide/unhide a date range based on the dates I have entered in some other cells. This way it makes it easier to see ONLY a certain week on an employee without unhiding all the cells, hiding the ones I don't need, and then printing it for a manager.

Timesheet dates are listed starting in A12
I'd like a checkbox next to each date range.
It would need to hide/unhide the rows based on the FROM/TO dates in:
AA1 (FROM) AB1 (TO) (checkbox)
AA2 (FROM) AB2 (TO) (checkbox)
etc...

timesheet.JPG
dates.JPG

Thank you!!!

**arlu1201** · 01-23-2012, 02:39 PM

It would be good if you attach the file instead of the .jpg images.

**b2995** · 01-25-2012, 02:33 PM

Sorry! Here's a sample. I haven't exactly decided the "placement" of the dates but this will give you an idea what I'm trying to do. This is just one worksheet but I have about 30 employees that I'd need to repeat this action for.

Timesheet_Sample.xlsx

**arlu1201** · 02-08-2012, 09:48 AM

At any point of time, how many date ranges will be selected?

**b2995** · 02-08-2012, 05:05 PM

It would have to reference the entire date range and only hide/unhide the range between the 2 dates I reference...in this case, each reference would be a week long.

Within a given week though, an EE may clock in and out several times for various reason:

In at 8AM
Out Lunch at 12PM
In Lunch at 1PM
Out at 5PM
Call In at 8PM
Out at 10PM

And to make things even worse, they make throw in a Dr's appt mid-morning. So to have a set number of rows designated for a payroll week doesn't happen, unfortunately.

Does that help explain it? :\

**b2995** · 03-20-2012, 01:03 PM

Ok...I'm trying to work through this for those that need a solution as well. I'm trying to come up with a work-around. This is not perfect since I still need a quick way to unhide specific work weeks, but this works really fast

In a column next to my dates, it places a 1 or a 0 depending if the date falls between 2 dates...plus one more week to give me room to add additional time for the same date (on call employees):
=IF(AND(B97>=StartDate,B97<=EndDate+7),1, 0)

Then, I created a macro using advanced autofilter to filter all the 1's.

Sub HideZeros()
' HideZeros Macro
ActiveSheet.Range("$A$11:$M$399").AutoFilter Field:=1, Criteria1:="1"
Range("D11").Select
End Sub

I then assigned it to a button which I then copied to each timesheet. Easy and fast, but not exactly what I needed...