Calculating time between two dates

[Guest]

I am working on a cancer research database in Excel, and have been told that
the following can only be done in Access, so here goes...

(simplified example of what I have now in Excel)
A B
1 Patient ID Date of Diagnosis
2 00001 10/1/1997
3 00001 10/1/1998
4 00002 09/1/2002
5 00002 09/1/2003
6 00003 09/1/2005

Each time a patient (uniquely and systematically identified by their own
patient ID) presents with a new cancer/tumor, they have a separate record
(row) in my excel spreadsheet with the corresponding Date of Diagnosis in
addition to other data. I want to calculate the time between these dates
(presently on different rows), and perhaps create a new spreadsheet with each
patient ID listed only ONCE, with "columns" such as "time from 1st diagnosis
to 2nd", "time from 2nd diagnosis to third", etc.

Can anybody help?

Thanks a bunch,

JG

 

[Joseph Meehan]

I am working on a cancer research database in Excel, and have been
told that the following can only be done in Access, so here goes...

(simplified example of what I have now in Excel)
A B
1 Patient ID Date of Diagnosis
2 00001 10/1/1997
3 00001 10/1/1998
4 00002 09/1/2002
5 00002 09/1/2003
6 00003 09/1/2005

Each time a patient (uniquely and systematically identified by their
own patient ID) presents with a new cancer/tumor, they have a
separate record (row) in my excel spreadsheet with the corresponding
Date of Diagnosis in addition to other data. I want to calculate the
time between these dates (presently on different rows), and perhaps
create a new spreadsheet with each patient ID listed only ONCE, with
"columns" such as "time from 1st diagnosis to 2nd", "time from 2nd
diagnosis to third", etc.

Can anybody help?

Thanks a bunch,

JG

You have have me down there with about 15 records over the last 35
years.

In any case, I suggest you start by importing the data into Access
directly from Excel, it goes easy. See File - Get external data - Import -
Excel.

You can then split the table using the Access database splitter that
should do the job of getting the data into two related tables which is what
you want to do first.

Frankly I am a little surprised that Excel can't do it, but I know less
about Excel than I know about Access and that is not all that much.

Next question will be how do you want to show the results? Printed
reports? Note, we will not store the results in a table, but we will allow
Access to recompute it each time we want to see it. (Usually Excel also
does this, but it looks like it is storing the result.)

 

[Guest]

Search for Doug Steele's "DateDiff" function in this news group. You will
find a link that takes you to the module and all the instructions, it works
very well.