• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Looking for a formula that checks if an ID falls between two dates and a single date

Hi All

Looking for a formula that checks if an ID falls between two dates and a single date.

(Please See Data attached)

Need this to help me state they were in care on the dates below or they went to the hospital between the care dates or they went back home between these dates.

I have an ID numbers and two dates see example below of when people were at are home:

In Care ID Dates crosscheck (small example Sample)

A B C

ID No. Start Date End Date

123 01/01/2019 10/07/2019

321 20/02/2019 05/06/2019

231 15/03/2019 11/08/2019

In Hospital Tab

I want a formula that cross checks these data and ID No. above. With another tab which gives customer ID Nos and dates, showing the start date they went in the end date the left hospital and if they went back into care and if these dates fall within the dates above.

Went home Tab

I have another tab that states the IDs and show one date they went home. Again, I want to know if this falls within again with the Care dates above sample.



I am grateful for any help in the matter or ideas.

Many Thanks

Leicester Fox
 

Attachments

  • Carers Dates Sample.xlsx
    10.2 KB · Views: 7
Perhaps these countifs may do (on sheet "In Care")
D2: =COUNTIFS(Hospital!$A$2:$A$4,'In Care '!A2,Hospital!$B$2:$B$4,">="&'In Care '!B2,Hospital!$C$2:$C$4,"<"&'In Care '!C2)
E2: = COUNTIFS('Went Home'!$A$2:$A$4;A2,'Went Home'!$B$2:$B$4,">="&B2,'Went Home'!$B$2:$B$4,"<"&C2)
 

Attachments

  • Copy of Carers Dates Sample.xlsx
    10.8 KB · Views: 4
Perhaps these countifs may do (on sheet "In Care")
D2: =COUNTIFS(Hospital!$A$2:$A$4,'In Care '!A2,Hospital!$B$2:$B$4,">="&'In Care '!B2,Hospital!$C$2:$C$4,"<"&'In Care '!C2)
E2: = COUNTIFS('Went Home'!$A$2:$A$4;A2,'Went Home'!$B$2:$B$4,">="&B2,'Went Home'!$B$2:$B$4,"<"&C2)
Hi Vletm
Thank you much appreciated for you time
The Leicester Fox
 
Back
Top