data anlysis

Paige

New member
Joined
Jan 19, 2018
Messages
1
Reaction score
0
Points
0
Hello. I am wondering if something is possible. Some example data is below. The rows are different patients. Columns B - K are the dates they came to visit the clinic. Columns L - U are the type of birth control they are using during that visit. (I am only interested in LARCs.) The last column is the date the patient gave birth. I want to know how much time has elapsed after the patient had a LARC to when she gave birth. For example, for patient 1, she had a LARC during visit 2 which was on 1/2/2011. Then she gave birth on 12/2/2011. So 11 months passed from the date she visited using LARC as a birth control to the time date she gave birth. Is there a way to calculate the minimum number of months that have passed from the time the patient was using a LARC to the time they gave birth for each patient?


visit1.date
visit2.date
visit3.date
visit4.date
visit5.date
visit6.date
visit7.date
visit8.date
visit9.date
visit10.date
visit1.BC
visit2.BC
visit3.BC
visit4.BC
visit5.BC
visit6.BC
visit7.BC
visit8.BC
visit9.BC
visit10.BC
birthdate
Person1
1/1/2010
1/2/2011
1/3/2012
1/3/2013
1/4/2014
1/5/2015
1/6/2016
1/6/2017
LARC
12/2/2011
Person2
2/1/2009
2/2/2010
2/3/2011
2/4/2012
2/4/2013
2/5/2014
2/6/2015
2/7/2016
3/7/2016
2/8/2017
LARC
LARC
LARC
LARC
LARC
3/8/2011
Person3
3/1/2007
3/2/2008
3/4/2009
3/6/2010
8/9/2013
Person4
3/1/2007
3/2/2008
3/4/2009
3/6/2010
3/8/2011
3/9/2012
LARC
LARC
7/1/2008
 
In row 2, say column W, this formula array-entered (that's Shift+Ctrl+Enter, not just Enter):
=DATEDIF(INDEX($A2:$K2,MAX(($L2:$U2="LARC")*(COLUMN(L2:U2)))-10),V2,"M")
and copy down.
You can surround the formula in an iferror function to hide the errors.
 
Back
Top