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

Netholan

New Member
Joined
Jul 17, 2013
Messages
6
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
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,005
datestart1dateend1datestart2dateend2doublebooked
tom06/01/201410/03/201401/03/201401/10/2014yes
bill06/01/201430/06/201403/07/201420/12/2014no
on another sheet
tomyes
billno
would this approach suit you?

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

Netholan

New Member
Joined
Jul 17, 2013
Messages
6
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

New Member
Joined
Jul 10, 2009
Messages
32
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:
Code:
COUNTIF(C5:NC5," ***** INT(NOW())")

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

Netholan

New Member
Joined
Jul 17, 2013
Messages
6

ADVERTISEMENT

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

New Member
Joined
Jul 17, 2013
Messages
6
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

New Member
Joined
Jul 17, 2013
Messages
6
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

New Member
Joined
Jul 10, 2009
Messages
32
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 :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,843
Members
413,944
Latest member
3xc3ln00b

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top