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

#### Leicester City Fox

##### Member
Hi All

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

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

• 10.2 KB Views: 7

#### GraH - Guido

##### Well-Known Member
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

• 10.8 KB Views: 4

#### Attachments

• 10.9 KB Views: 5

#### Leicester City Fox

##### Member
Leicester City Fox
This shows FALSE ... if something ... should be otherway.
Hi Vletm

Good Morning

Thank you for this much appreciated.

You a Star

The Leicester Fox

#### Leicester City Fox

##### Member
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