Calculate between End date and next start date (1 Viewer)

[Angelflower]

I have a table that has the following feilds:

Person Start Date End Date
John Smith 10/1/2006 1/14/2007
John Smith 2/18/2007 5/31/2007

What I want to do is calculate the number of months between the 1st end date and the 2nd start date. Any ideas on how to do this?

Thank you in advance for your help with this!

 

[Uncle Gizmo]

Look at the DateDiff Function

 

[raskew]

unclegizmo -

Interesting problem. Played with it for a while. Naturally, the DateDiff function returns the desired result, given two dates. However, bombed-out trying to automate the selection of dates for each individual. Although unsaid, it would seem that an individual could have more than two periods of employment. Any thoughts?

Best wishes - Bob

 

[Angelflower]

Yes a person could have two periods of employment or as in the case of my table a person could have more that two inpatient hospital stays. What I am trying to figure out is how long of time has elapsed between those visits. I use the datediff function to determine the space of time (length of stay) between start date and end per visit. In my example what I want to try and figure out is how much time has elapsed between 1/14/2007 (end of 1st visit) and 2/18/2007 (start of 2nd visit).

 

[Uncle Gizmo]

Yes, I see, I didn't read your question properly!


>>>Although unsaid, it would seem that an individual could have more than two periods of employment. Any thoughts?<<<<

I think the first thing to do would be to make sure that there were two person entries, I wouldn't worry about one or more than two for the moment.

Then take the ID field (which you haven't got) this would have to be an auto number field ascending , not Random. Then extract the end date of the first record and compare it with the start date of the second record.

I don't know but I've got an idea I answered a question similar to this a few months ago I will go and have a look......

 

[raskew]

Hi -

Certainly one problem I see is identifying persons by name. That's just frought with disaster. If one time he's listed as John Smith and another as Jon Smith - oops, we're talking potentially of two different people. They need a patientID (SSN or something similiar).

Given that you're probably dealing with a bunch of patients and you want to process the information without resorting to pencil and paper, the idea is to develop an automated process.

Still a little lost. Have published solutions on patients and visits (although not on time between visits), am trying to go back and find them. This gets into previous record vs. current record. Never a really fun thing to program.

Bob

 

[Uncle Gizmo]

I found the "similar" question I answered recently, it's not handling dates its handling numbers, I haven't studied it to see if it is suitable to sort out your problem, but my instinct is it may well be worth having a look at for ideas. Nearly forgot the link its here.

Cheers Tony

 

[CraigDolphin]

Well, you could try:

MyMonths: DateDiff("m",DMin("[Start Date]","[COLOR="Red"]Table1[/COLOR]","[Person]=[COLOR="blue"]'[/COLOR]" & [Person] [COLOR="blue"]& "'"[/COLOR]),CDate(DMax("[End Date]","[COLOR="red"]Table1[/COLOR]","[Person]=[COLOR="blue"]'[/COLOR]" & [Person] [COLOR="blue"]& "'"[/COLOR])))

Obviously substitute the table name (in red) and modify field names as appropriate. Also, the point about using a text, name field to identify the person is a good one. It would be much better to use a unique numeric identifier for the person. If you do that, remove the single quotes and the other bits in blue.

 

[Angelflower]

Thank you all for your comments and suggestions. I have now plenty to work with this morning. As for the comments regarding using a name...yes I agree... I should have included the ID of the table instead of my made up name... I only posted part of my whole table but I get the idea that using a distinct ID such as an auto number. I will remember next time to not use any made up names but to use an ID instead.