# Dates: Compare dates from two sheets

1. ## Dates: Compare dates from two sheets

I have two sheets. One has a list of leaves an employee is on in the year. Then another list of employee assignments on cases. I am looking to see during the time a person is on a case, are they on leave during any point of that assignment?

EmpID Name Type Start End
37864 Bob On Leave 5/20/2019 5/24/2019
37864 Bob On Leave 5/25/2019 6/14/2019
37864 Bob On Leave 6/15/2019 6/24/2019
37864 Bob On Leave 6/25/2019 7/8/2019
67543 Mary On Leave 7/1/2019 9/1/2019

Ecode Name Case Start Date End Date Is Person On Case While on Leave?
37864 Bob N5LS 4/1/2019 5/27/2019 Yes
37864 Bob Q99 1/28/2019 2/4/2019 No
37864 Bob D6R 2/4/2019 2/12/2019 No
37864 Bob PEF 1/14/2019 1/18/2019 No
67543 Mary HY8H6/1/2019 7/15/2019 Yes

Essentially I need a way to automatically determine if a person is on leave while on a case assignment.

2. ## Re: Dates: Compare dates from two sheets

because i'm using an array formula and using the whole column to range it up will slow the calculation speed, i suggest you convert the Leave worksheet into a Table to make it dynamic.

use the formula:
=IF(SUMPRODUCT((Leave!\$D\$2:\$D\$6>=D2)*(Leave!\$D\$2:\$D\$6<=E2)+(Leave!\$D\$2:\$D\$6<D2)*(Leave!\$E\$2:\$E\$6>=D2)*(Leave!\$A\$2:\$A\$6=A2)),"Yes","No")

3. ## Re: Dates: Compare dates from two sheets

One more approach.
In F2 then copied down.

=IF(SUMPRODUCT((Leave!\$A\$2:\$A\$6=\$A2)*((Leave!\$D\$2:\$D\$6<=\$E2)*(Leave!\$E\$2:\$E\$6>=\$D2)))>0,"Yes","No")

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1