Rounding Down time elapsed to nearest 6 months

[panesai1]

Hi all,
The context is this: Patients are invited for a long-term (8 years) Research study on a new treatment.
Each patients are followed-up with Hospital clinic visits every 6 months (for 8 years) to track their progress.
Each patient begins their participation in the study at different times.
e.g. The first patient started his treatment 20-Jun-2012 and the last patient started his treatment 31-Dec-2015.
Let's say there are 100 patients (n=100) in total enrolled in this study.

I have an Excel sheet similar to the below. I have only shown the columns up to 36months, just so you get the idea. It actually goes up to 8 years on my XL sheet.

In Row 4, I already have a formula which shows me when each patients 6 month visit will be, 12 month visit will be, based on my input of their treatment date in Column B. (i've shown the example of amy formuala in cell C2, which works great - no issues on this).

What I want to be able to know is: At any date projected date (written in Cell B1), how many months of follow up visits will each patient have completed. The result should go in Column J.

I have tried to use DATEIF. This only partly works, because it calculates the time elapsed between date of treatment and the date in B1, but incorrectly rounds down to the nearest month, rather than ROUNDING DOWN to the last 6 monthly interval. This is why the context is important to note.... because the data generated from each patient is only available to me when that patient actually attends their 6 monthly clinic visit.

For instance, a DATEIF formula between start date (B4) and end date (B1), might generate a result such as "2 yrs 8 months". Though accurate, it is not useful for me, because I would prefer the result to tell me that Patient 001 has progressed through the 30 month (2 years and 6 month) clinic follow up visit.


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Projected date:[/TD]
[TD]dd-MMM-YYYY[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]month[/TD]
[TD]month[/TD]
[TD]month[/TD]
[TD]month[/TD]
[TD]month[/TD]
[TD]month[/TD]
[TD]....[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Patient number[/TD]
[TD]Date of treatment [/TD]
[TD]6[/TD]
[TD]12 [/TD]
[TD]18 [/TD]
[TD]24 [/TD]
[TD]30 [/TD]
[TD]36 [/TD]
[TD].....[/TD]
[TD]Amount of Follow up data available[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]001[/TD]
[TD]20-Jun-2012[/TD]
[TD]=DATE(YEAR($B4),MONTH($B4)+C3,DAY($B4))[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]?????[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]002[/TD]
[TD]29-Aug-2013[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Hope someone out there can help?

Many thanks

 

[Dave Patton]

consider
=LOOKUP(YEARFRAC(B4,B1,1),rTable)

rTable is a named range with the text that you require
one line first column 2 and next "2 Years" without the brackets.

 

[panesai1]

You da man Dave --- Thanks very much .
Worked a treat!

I created a rTable as follows, and used the Lookup formula you suggested.
(in the Vector column, I left out the words "years" or "months", so that I can apply SUBTOTAL or SUM calculations on all the values that are in Column J).

Thanks again,
panesai1



[TABLE="width: 156"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]value[/TD]
[TD]vector[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]0.08[/TD]
[TD]0.08[/TD]
[/TR]
[TR]
[TD]0.17[/TD]
[TD]0.17[/TD]
[/TR]
[TR]
[TD]0.25[/TD]
[TD]0.25[/TD]
[/TR]
[TR]
[TD]0.33[/TD]
[TD]0.33[/TD]
[/TR]
[TR]
[TD]0.42[/TD]
[TD]0.42[/TD]
[/TR]
[TR]
[TD]0.5[/TD]
[TD]0.5[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1.5[/TD]
[TD]1.5[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2.5[/TD]
[TD]2.5[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3.5[/TD]
[TD]3.5[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]4.5[/TD]
[TD]4.5[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]5.5[/TD]
[TD]5.5[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]6.5[/TD]
[TD]6.5[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]7.5[/TD]
[TD]7.5[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]8[/TD]
[/TR]
</tbody>[/TABLE]