How can I check a set of dates for possible duplication using formula

[Netholan]

I have a worksheet which J3:NJ3 lists the dates for the calendar year. (Example - J3 = 1/1 , K3 = 1/2 , etc. )

A3:C3 are my headers for project information (project name, manager,etc)

In D3 I will be entering employee names. in E3 I will be entering Project start dates, and in H3 will be end dates. These dates are selected via validation lists of the dates listed in J3:NJ3, and it is set to auto fill the adjacent date range with an "X" after the start and end dates have been selected.

Now I also have another worksheet which is a pivot table that will auto update upon any workbook change, and will show each employee and all of the dates in which they are scheduled to work. I am using conditional formatting on this sheet to highlight any dates that the selected employee is double booked on two or more projects.

I want to keep this second worksheet as is, but to avoid having to flip flop back and forth between tabs to find out of someone is double booked, I would like to add a column (I) which will either say "Available" or "Double Booked" . Is there a formula that I can use for this? I thought about Vlookup, but I don't believe I know of a way to write a Vlookup formula that can check a dynamic range of entered dates. I thought perhaps a nested Vlookup/Hlookup, but not quite sure if that will work either.

Suggestions?

Shaun

 

[oldbrewer]

	datestart1	dateend1	datestart2	dateend2	doublebooked
tom	06/01/2014	10/03/2014	01/03/2014	01/10/2014	yes
bill	06/01/2014	30/06/2014	03/07/2014	20/12/2014	no
		on another sheet
		tom	yes
		bill	no
					would this approach suit you?

<colgroup><col><col span="4"><col></colgroup><tbody>
</tbody>

 

[Netholan]

Ok. Here is what I have come up with. On the tab which shows if someone is double booked, I added a column which will count the number of cells which contain "DB" (double booked) , and on TAB 1 I have a nested "IF(Vlookup)" formula that says if the result of that formula is >0 , indicate they are double booked.

Sheet2
Cell A5= Employee Name
Cell B5 = Countif(C5:NC5,"DB")

Sheet1
Cell D4 = Employee Name
Cell I4 = IF(VLOOKUP(D4,Sheet2!A5:B150,2,FALSE)>0,"DB","Available")

This works perfectly. In this case, I don't necessarily need to know the date range because once the range is entered, if it comes up DB, then we automatically know that the entered range is double booked somewhere.

However..in some cases, not often, we will go ahead and double book that employee. Now what I need to do is figure out if I can have the Countif formula start it's count at todays date going forward, and not include past dates. Currently it will now always show that employee as double booked.

 

[baldmosher]

However..in some cases, not often, we will go ahead and double book that employee. Now what I need to do is figure out if I can have the Countif formula start it's count at todays date going forward, and not include past dates. Currently it will now always show that employee as double booked.

For this, VBA makes it easy

Consider using a dynamic range, using the following to count the number of cells to exclude:

COUNTIF(C5:NC5," ***** INT(NOW())")

replace ***** with the 'less than' symbol, it keeps cropping my post!!

 

[Netholan]

Thank you for the response. Unfortunately that way would force me to re-do my entire workbook and I am trying to keep it in the format I have worked on. Here is a sample to help figure this out.

D F H I J K L M N O
3 Emp Start End Avail 1/1 1/2 1/3 1/4 1/5 1/6
4 Bill 1/1 1/3 DB X X X
5 Tom 1/3 1/6 Avail X X X X
6 Bill 1/3 1/6 DB X X X X
7

Now instead of the IF(Vlookup(D4,Sheet2!A5:B150,2,FALSE)>0,"DB","Available")
Is there a way to write a formula that only looks for the number of "DB" counts starting at todays date? Today()

 

[Netholan]

Wow..that post did not work at all..I had it all lined up prior to posting. My apologies for that mess.

Thank you Baldmosher. I will give that a try and see if i can get that to work.

 

[Netholan]

Baldmosher:

My challenge here is being fairly basic with my VBA. As noted above, I have code that auto refreshes my Pivot tables. I know enough about how to add code to a sheet and some basic functions, but this one is a bit complex, as I do not know how to code it where it will look for the employee an the second sheet, find todays date in the header (or the entered start date for that matter), and only look at those cells going forward.

 

[baldmosher]

It's easy to specify a range to be searched in VBA if you name the range in Excel, just select column F and replace the cell reference with "SomeRangeName" and then refer to that with Range("SomeRangeName") -- that simple step makes it much easier to start searching. Best to use INDEX and MATCH, i.e. Application.Index(Range("ResultRange"), Application.Match("Bill", Range("SearchRange"), 0)) works like a VLOOKUP but with specific search and result ranges. MATCH returns the cell number within the search range and INDEX returns the result from that numbered cell within the result range. You can put INDEX and MATCH on your spreadsheet in formulas too, and refer to the entire column instead of typing "Bill".

I started helping you out a bit with the VBA but then I realised I would have to write it all out for you and I only have a few mins to post something quick