EXCEL IF AND OR (Please help in creating a formula)

[jotheghostman]

Hi

Would anyone please help me compose formulas for these cases:

<o></o>
1. I have a database of medical history ofpatients including dates of their visit to hospital. I need to use pivot on itand group them on a monthly basis. However, not necessary calendar months as Januaryto December. Instead, I need to start with a certain contract period, forexample, August 20, 2016 as beginning of their visit.<o></o>
<o> </o>
If patient visited the hospital from August20, 2016 to September 19, 2016, then formula result should be “1^stMonth”, if visit was September 20, 2016 to October 19, 2016, then it should be “2^ndmonth”. <o></o>
<o> </o>
Is it possible that YEAR is not adeterminant? The basis would just be the MONTH and DATE, because my databasestarts from 2014 to date. I would just want “1^st Month” to “12^thMonth” result regardless of year.<o></o>
<o> </o>
2. Also, I have another database of list ofpatients and their families with PATIENT NAME (Column A), AGE (Column B),RELATION (Column C). RELATION states if patients is PRINCIPAL PATIENT, PARENT,SIBLING, SPOUSE, CHILD. <o></o>
I need a conditional formula forRELATION and AGE. Say, if RELATION states PRINCIPAL PATIENT, and AGE is 0 to 65yearsand 6 months, then formula result is “QUALIFIED”. If PARENT or SPOUSE and ageis 0 to 65 years and 6 months, then result is “QUALIFIED”. If SIBLING or CHILDand age is 0 to 18 years and 6 months, then result is “QUALIFIED”. Otherwise, “INELIGIBLE”<o></o>
I have been trying to compose and search in Google, butIve given up. Please help me on the above.<o></o>
This would truly change my work life.<o></o>
Thank you.<o></o>

 

[mse330]

Hey jotheghostman & welcome to the board.

Regarding your first request, the below formula should show numbers from 1 to 12 regardless of the years difference

=MOD(DATEDIF(H3,I3,"M"),12)+1
H3 = Contract Date
I3 = Visit Date


I am not really clear on your 2nd request, what do you mean by the 6 months ?
"AGE is 0 to 65years and 6 months"

 

[jotheghostman]

Hey jotheghostman & welcome to the board.

Regarding your first request, the below formula should show numbers from 1 to 12 regardless of the years difference

=MOD(DATEDIF(H3,I3,"M"),12)+1
H3 = Contract Date
I3 = Visit Date


I am not really clear on your 2nd request, what do you mean by the 6 months ?
"AGE is 0 to 65years and 6 months"

Thank you very much mse330! As I did the first formula, it looks like I needed to insert another column and specify the start of contract date. It determines the 1st to 2nd months! Wow!

With my second case, I meant with age as 65 years old and a half - that's why I specified 65 years and 6 months. Or, lets omit the half year and say, "65 years old".

I truly appreciate your reply. Just had one fish bone outta my throat!

Thanks again and hope to hear from you soon.

 

[mse330]

You are welcome

I think the below formula might address your 2nd request. You may need to adjust based on the conditions but I have provided the logic

=IF(AND(OR(C2="PRINCIPAL PATIENT",C2="PARENT",C2="SIBLING",C2="SPOUSE",C2="CHILD"),B2<=65),"QUALIFIED","INELIGIBLE")

C2 = Relation
B2 = Age

Regards