HELP!! Using DAYS360 to calculate age at visit, follow-up days for list of patients

mitone

New Member
Joined
Jul 16, 2012
Messages
7
Hi, I am updating a very large spreadsheet containing diagnostic information about patients. I need to add two columns of information: "Age at first visit" and "Follow-up Day" (the number of days after the first visit). To do this I am using the following formulas: "Age at first visit": =DAYS360(Date of Birth,Earliest Visit Date)/360 "Follow-up Day" =DAYS360(Earliest Visit Date,Visit Date in question).

I work at an eye clinic, so each patient actually usually has 2 visits per visit date (one for each eye), if that makes sense. As I said I have many, many columns of information, but the ones that are of use in this question are as follows:

FirstName / Lastname / Patient ID (unique to each patient) / Eye (Right or Left) / Age at first Visit / Follow-up Day / Visit Date (Chronological) / DOB

As I said, I don't think all of the categories above need to be used, but these are the ones that I thought might be useful.

I'm really having a hard time coming up with any easy way to do this, and I don't know if it is even possible to have a click-and-dragable formula.

Any help would be appreciated!!
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

pplstuff

Well-known Member
Joined
Mar 9, 2012
Messages
951
For age at first visit =year(DateOfFirstVisit)-year(Birthday) <--format is as number (it might try to format as a date at first)

What do you want the follow up day to be?
You can always do =(DateOfVisit)+#ofDaysToNextVisit
or.
=FutureDate-LastVisitDate <--format as number

I'm not sure if this answers your question, let us know.
 

mitone

New Member
Joined
Jul 16, 2012
Messages
7
Thank you for your speedy reply. I apologize, I probably didn't explain my predicament very well. I think the DAYS360 formulas work okay, and the ones you gave me above, would work just as well. My problem is in how to make it into a click-and-dragable formula, because I have to do this for 5000+ rows of patent info. So here's an example of the layout, and what I need:

First Name / Last Name / Patient ID / Eye / Age at first visit / Follow-up Day / Visit Date / DOB
John / Doe / 1 / Right / -- / -- / 1/1/2000 / 1/1/1950
John / Doe / 1 / Right / -- / -- / 7/1/2000 / 1/1/1950
John / Doe / 1 / Right / -- / -- / 2/2/2001 / 1/1/1950
John / Doe / 1 / Left / -- / -- / 1/1/2000 / 1/1/1950
John / Doe / 1 / Left / -- / -- / 7/1/2000 / 1/1/1950
John / Doe / 1 / Left / -- / -- / 2/2/2001 / 1/1/1950
Jane / Smith / 2 / Right / -- / -- / 3/3/2001 / 2/2/1945
Jane / Smith / 2 / Right / -- / -- / 4/4/2001 / 2/2/1945
Jane / Smith / 2 / Left / -- / -- / 3/3/2001 / 2/2/1945
Jane / Smith / 2 / Left / -- / -- / 4/4/2001 / 2/2/1945

So there is an example, for John Doe, the DAYS360 formula I am using in the "Age at first visit" column would return a value of 50.0 (because we want it in years to one decimal point), and I would want that column to return that same value for each cell corresponding to John Doe, but I want it to change (automatically I hope) to reference Jane Smith's information and would return a value of about 56.1 years and give that number for each cell corresponding to Jane Smith. So on, and so forth for the rest of the 5,000+ lines.

Similarly, the "Follow-up Day" would return a value of "0" for the first Follow-up Day, but then calculate the number of days that have passed since the first exam date. For example, the DAYS360 formula I am using would return a value of 180 (because we want it in days) for the second exam date and a value of 391 for the third exam date, so on and so forth.

The problem I am having is how to establish the formula/wording so that as I click and drag it always references the first exam date for that corresponding patient, but when I reach a new patient's line, it switches to always reference the first exam date for that patient.

I hope that makes sense!

Thank you SO SO much!!!
 

mitone

New Member
Joined
Jul 16, 2012
Messages
7
Sorry, but as an update, I figured out a good way for the "Age at first Visit" column, but I'm still having problems thinking of a good way for the "Follow-up Day" column.
 

pplstuff

Well-known Member
Joined
Mar 9, 2012
Messages
951
If your data is as you posted above, you should have no trouble double-clicking the bottom-right corner of a cell and having the formula drop down the column.

I stand by the formulas I posted above. They will both work in your situation. What you are missing is a "NextVisitDate" How can you calculate how many days until their next visit if you don't know when the next scheduled visit is?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,494
Messages
5,602,004
Members
414,490
Latest member
Rip181

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top