Formula for calculating patient days with selected date range

[HospitalOfficer]

I have been struggling with a formula. I have a spreadsheet where I need to calculate the number of days between two dates. However that calculation of days should only be calculated if the dates fall within a specific date range.

For instance, I need to calculate the Length of Stay (LOS) for each patient whose date range falls within date range of example:
Admit Date 03/01/2014
Discharge Date 03/31/2014
Where the date of discharge is not counted unless the patient was only admitted for one day.

Patient Name Admit Date Discharge Date Length of Stay
Patient A 02/28/2014 03/01/2014 0 (This day would be counted in February)
Patient B 03/01/2014 03/03/2014 2
Patient C 03/05/2014 03/12/2014 7
Patient D 03/31/2014 04/01/2014 1
Patient E 02/27/2014 03/02/2014 3
Patient F 03/29/2014 04/03/2014 3

Any assistance would be appreciated.

Thank you,

HospitalOfficer

[sktneer]

Not sure exactly how do you want it to get done, try this to see if this is what you are trying to do...

In D6.....

Please Login or Register  to view this content.

and drag down.

[gak67]

Please Login or Register  to view this content.

With the start of the date range in D1 and the end of the date range in E1, the admission date in B2 and the discharge date in C2. It can then be dragged down.

[playaller]

This is the formula I get but it does not match your last date. Please explain how it is 3 for Patient F? I get 5 or 2.


Paste in D6 and copy down.

Please Login or Register  to view this content.

[gak67]

I Have revised my formula, but I also think you have a calculation wrong, but for Patient E.

Please Login or Register  to view this content.

Patient E is only there for 2 days of the period specified, but you said to not include the discharge day, so it should be 1.

[gak67]

This is the formula I get but it does not match your last date. Please explain how it is 3 for Patient F? I get 5 or 2.

The three days are 29, 30 and 31 March.

[playaller]

Got cha. Try this:

Paste in D6 and drag down:

Please Login or Register  to view this content.

[HospitalOfficer]

Hi

Thank you for your response. When calculating patient days, I can only count days that occur within the required period, excluding the date of discharge unless the patient only had a one day stay. For instance a patient from 2/27 - 3/1, would not have any patient days in the month of March because they were discharged on the 1st. However they would have two patient days for the month of February, 2/27 and 2/28. Since I am referencing March. I would not expect to see any days.

The formula is calculating correctly for some, but not for others. See attachment.

See Excel Export.

Thank you.
D

[HospitalOfficer]

You are correct, my calculation is wrong is Patient E. I will try this formula and see if it works.

[HospitalOfficer]

gak67

I apologize, I am new to the forum. I think your formula worked. I am going to test try it today on our EOM stats and let you know.

HospitalOfficer

[Sam Capricci]

this formula seems to match what you want...
=IF(A6=B6,1,IF(A6<$A$1,DATEDIF($A$1,B6,"yd"),IF(A6>=$A$1,(DATEDIF(A6,B6,"yd")))))

[gak67]

this formula seems to match what you want...
=IF(A6=B6,1,IF(A6<$A$1,DATEDIF($A$1,B6,"yd"),IF(A6>=$A$1,(DATEDIF(A6,B6,"yd")))))

This will return 1 for all instances of when the admission date equals the discharge date, but the OP only wants those in the date range specified.

[popipipo]

=IF(AND(A6=B6,A6<$A$2,A6>=$A$1),1,MIN(B6,$A$2)-MAX(A6,$A$1))

Try this one

Change A2 from 31-3-14 in 1-4-2014 (dd-mm-yyyy)

[playaller]

It seems the layout of your uploaded document differs from what I started as now there is no patient column. I have updated my code and attached your document with the coding as well. You can paste the below code in C6 and pull down.


Paste in C6 and pull down.

Please Login or Register  to view this content.

[sktneer]

This formula should meet all the criteria you have set.

Please Login or Register  to view this content.

See the attached sheet.