Determine if entry occurs previously and calculate time interval between recurring entries

[arkham618]

I'm building a workbook to track patient demographics for a psychiatric ward. I want to be able to determine if a patient has been previously admitted during the calendar year, and if so, to calculate the number of days since the last admission. The workbook is structured so that each month has its own worksheet (labeled Jan, Feb, Mar, Apr, and so on, through Dec), each of which contains dates of admission in the range B3:B52, and unique patient ID numbers in the range H3:H52.

In the range M3:M52, I wanted Excel to return a "Y" if the patient had previously been admitted to the ward, and an "N" if he hadn't. My solution involved creating a hidden column (I) and inserting the following formula in the range I3:I52:

=IF(ISBLANK(H3),"",H3&CHAR(SUMPRODUCT(COUNTIF(INDIRECT("'"&Admin!$A$2&"'!$H$3:$H$52"),H3)+COUNTIF($H$3:$H3,H3)+96))

The segment SUMPRODUCT(COUNTIF(INDIRECT("'"&Admin!$A$2&"'!$H$3:$H$52"),H3) references a worksheet (Admin!) on which is listed all of the monthly worksheets, from Jan(uary) to Dec(ember), in the range A2:A13. It is omitted completely in the month of January (which has no preceding months), and becomes SUMPRODUCT(COUNTIF(INDIRECT("'"&Admin!$A$2:$A$#&"'!$H$3:$H$52"),H3) for all months after February, with the hash corresponding to the list entry for the most recent month (3 for Feb, 4 for Mar, and so on).

This takes the patient ID number in column H and appends a unique character (a, b, c, d, ...) at the end of each occurrence. So the first occurrence of ID#123456 becomes 123456a; the second occurrence, 123456b; and so on. In the range M3:M52, I then inserted the following:

=IF(ISBLANK(H3),"",IF(I3<>(H3&CHAR(97)),"Y","N"))

Which returns an "N" for the first occurrence of an ID# (123456a) and a "Y" for each subsequent occurrence (123456b, 123456c, and so on).

All well and good. The difficulty I am facing now, however, is in devising a formula that will calculate the interval between discharge and readmission. I need to figure out how to use DATEDIF to calculate the number of days between the current admission date (found in B3:B52) and the most recent discharge date (found in R3:R52) for patients with a "Y" (indicating a readmission) in M3:M52. The resulting formula must, like the one in I3:I52, be able to reference multiple sheets in order to account for all preceding months in the calendar year. Any suggestions?

[rossg]

suggestion is to put up a sample workbook

if you have a date of discharge you cn use =today()-discharge date to work out days since discharge.

then use if statements to determin whether this is done or not

[arkham618]

suggestion is to put up a sample workbook

if you have a date of discharge you cn use =today()-discharge date to work out days since discharge.

then use if statements to determin whether this is done or not

The file is on a computer at work. I'll post a sample once I have access to it.

TODAY() won't work because it's a value that changes from day to day. I need to find a way to subtract the most recent discharge date from the most recent admission date for patients that have been readmitted during the same calendar year (i.e., 2013). These dates are fixed values (e.g., discharged 01/07/13, readmitted 02/07/13 -- an interval of 31 days). However, they may occur in separate months, and thus on separate worksheets. Furthermore, if a patient has been readmitted more than once during the calendar year, I need the formula to reference only the most recent discharge, and not earlier ones.