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

Netholan

New Member
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

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

oldbrewer

Well-known Member
 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

New Member
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
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

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

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
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
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
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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,168,025
Messages
5,856,912
Members
431,837
Latest member
megantang

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.

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

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