start/end dates between/overlap multiple start/end dates

**garricko** · 04-29-2010, 06:29 PM

Hello,

I've been trying to find a way to do this for days now and pretty much stuck.

I am trying to assign tasks to people in a spreadsheet using a start date and end date. What I'm trying to do is have these start/end dates check against their time off start and end dates. The problem is people have multiple start/end dates for time off.

I want to show a value or conditional format that will alert me that I have scheduled a task that overlaps or is between the start/end time off dates. Is this possible?

**day92** · 04-29-2010, 06:54 PM

Garricko,

It might be helpful to upload some dummy data so we can better understand your issue.

**daddylonglegs** · 04-29-2010, 07:01 PM

At the simplest level then if you have time off date periods in A3:B10 and task periods in E3:F10 then you can select E3:F10 and use this formula in conditional formatting

=AND(COUNT($E3:$F3)=2,SUM(($A$3:$A$10<=$F3)*($B$3:$B$10>=$E3))>0)

This will format any task date period that overlaps with any time off date period. Note: formatting only appears once two dates are entered in both column E and F

This could be expanded to check for specific names if you have task/time off dates for multiple people, see attached

**garricko** · 04-29-2010, 07:29 PM

Thank you DaddyLongLegs!

Yes, sorry I did not put an example but DaddyLongLegs got it pretty much what I was looking for. But I am interested in the expanding part with names, can you give me an idea what you mean?

**daddylonglegs** · 04-29-2010, 07:45 PM

If I add two names columns, one each for time off and tasks, then formula changes to

=AND(COUNTA($E3:$G3)=3,SUM(($A$3:$A$10=$E3)*($B$3:$B$10<=$G3)*($C$3:$C$10>=$F3))>0)

and clearly that means that the task dates for a named individual will only be checked against the time off dates for that person

see revised attachment

**garricko** · 04-29-2010, 08:22 PM

This is great!

Just out of curiosity, if I was to do this via a formula rather than a conditional format would it be much more difficult? I tried pasting the formula and editing the cell references but I noticed that if the timeoff start date and end date is the same, the formula says it is FALSE when it should be TRUE.

**daddylonglegs** · 04-30-2010, 05:40 PM

The formula I suggested is an "array formula". That means that if you use it in a cell you need to use CTRL+SHIFT+ENTER so that curly braces appear around the formula (that isn't required within conditional formatting).

If you want to use in a cell then better to use SUMPRODUCT rather than SUM so that it doesn't need "array entering" - change formula to this:

=AND(COUNTA($E3:$G3)=3,SUMPRODUCT(($A$3:$A$10=$E3)*($B$3:$B$10<=$G3)*($C$3:$C$10>=$F3))>0)

**garricko** · 04-30-2010, 09:49 PM

Thank you very much DaddyLongLegs!!! Works perfectly.

**Peter Chang** · 03-04-2012, 09:25 PM

Hi,

Can anyone help? Scenario:
a) Duration is fixed eg 3 months, 1 year
b) when I key in the latest date, it will automatically change the date 3 months or 1 year ago.
Eg. Duration 3 years, when I key in 1/3/12 (present date) the date required is 1/12/11 (3 month ago)
When I change the present date to 1/4/12, the date required is 1/1/12.
What is the formula?
Thanking you in anticipation.

**daddylonglegs** · 03-05-2012, 08:17 AM

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.