+ Reply to Thread
Results 1 to 3 of 3

Dates: Compare dates from two sheets

  1. #1
    Registered User
    Join Date
    07-02-2019
    Location
    Boston, MA
    MS-Off Ver
    2016
    Posts
    1

    Talking 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 ADP43/5/2019 3/15/2019 No
    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.
    Attached Files Attached Files

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,127

    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.
    https://www.youtube.com/watch?v=_c4BomTi1Fo

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

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    mysore
    MS-Off Ver
    Excel 2007
    Posts
    4,873

    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")
    Attached Files Attached Files
    Last edited by kvsrinivasamurthy; 07-03-2019 at 12:37 AM.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Reconcile sheets with calendar dates in rows vs dates in columns.
    By ExcelWizardlvl1 in forum Excel General
    Replies: 10
    Last Post: 08-13-2018, 10:42 PM
  2. [SOLVED] Compare two dates with two other dates in a whole column.
    By maxanalyst in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-12-2016, 02:27 PM
  3. [SOLVED] Compare Dates/Times on Two Separate Sheets
    By dkcel in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 04-30-2013, 01:30 PM
  4. Compare Dates in two excel sheets
    By kate.middleton1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-17-2013, 02:07 AM
  5. [SOLVED] Auto filling dates based on previously entered dates and averaging numbers if dates equal
    By grambograham in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-11-2012, 03:21 PM
  6. Active formula that reads dates and compare them with week sheets
    By Ramzes in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-22-2010, 08:59 AM
  7. Compare dates to ranges of dates
    By Santed593 in forum ExcelTip.com Feedback / Comments / Suggestions
    Replies: 1
    Last Post: 08-05-2005, 12:22 PM

Tags for this Thread

Bookmarks

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