Results 1 to 3 of 3

Thread: Cross checking booked holiday with project dates
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2016
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Cross checking booked holiday with project dates

    Hi

    I have sheet1 (project dates) set up like this:

    Start Date | End Date | Initial
    15/01/2019 20/01/2019 BF
    16/01/2019 16/01/2019 FB
    01/02/2019 14/02/2019 BF
    15/02/2019 28/02/2019 FB

    ...and sheet2 (booked holiday) set up like this:

    Holiday Date | Initial
    16/01/2019 BF
    15/01/2019 FB
    14/02/2019 BF
    14/02/2019 FB

    I would like to flag on sheet1 any projects that have date ranges which clash with the booked holidays (using the Initials as an identifier for clashes). Hopefully that makes sense? I've spent a couple of hours scouring the internet for an answer to this relatively simple function but I haven't had any luck.

    Thanks for your help!

  2. #2
    Board Regular Rijnsent's Avatar
    Join Date
    Oct 2005
    Location
    Utrecht, Holland
    Posts
    1,052
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cross checking booked holiday with project dates

    Hi Fawjon,
    I guess the COUNTIFS formula should be able to give you an answer.
    E.g. like so (formula for sheet1, cell D2):
    =COUNTIFS(sheet2!B:B,C2,sheet2!A:A,">="&A2,sheet2!A:A,"<="&B2)
    Cheers,
    Koen
    You can't post attachments here, but you can help me helping you by posting a screen shot directly in your post with any of those tools.
    Otherwise use dropbox/google drive/etc to get your file accross (not preferred). For code, put it inside these tags: [ CODE][/CODE]. Do check the forum rules.
    Finally, please show that you made an effort to solve your problem: Yes, I like to help, but am not going to do your job.

  3. #3
    New Member
    Join Date
    Jul 2016
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cross checking booked holiday with project dates

    Quote Originally Posted by Rijnsent View Post
    Hi Fawjon,
    I guess the COUNTIFS formula should be able to give you an answer.
    E.g. like so (formula for sheet1, cell D2):
    =COUNTIFS(sheet2!B:B,C2,sheet2!A:A,">="&A2,sheet2!A:A,"<="&B2)
    Cheers,
    Koen
    Thanks for the reply Koen. I eventually found a way to do it using sumifs

    =IFERROR(IF(SUMIFS(holidaylist!C:C,holidaylist!A:A,">="&[@Start],holidaylist!A:A,"<="&[@Finish],holidaylist!B:B,[@[PM 1clash]])>0,"Holiday",""),"")

    (holidaylist!C:C = value of 1 for each record, holidaylist!A:A = holiday dates, holidaylistB:B = employee initials, @start = project start date, @finish = project end date, PM1clash = project employee initial)

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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