Number of Days in a Date Range

[Guest]

I am creating a spread sheet to identify the number of days a patient was on
a particular ward. sometimes, the patient is a resident for a span of time
that includes several different months. What I need a formula that will break
down the number of days the patient was in admittance for each different
month:

Example:

John Doe 1/5/05 through 3/7/05
What is required is to calculate the number of days for each month,
separately.
Jan Feb Mar
? ? ?

 

[Dave O]

This can be facilitated if you use actual dates for the Jan Feb Mar
headers (format them as mmm-yy). The answer will require a fair amount
of IF this cell is greater than or equal to that cell, etc.

How will you spreadsheet expand to accommodate people who arrive on
December 31, 2005 and depart January 2, 2006?

 

[Dave O]

This can be facilitated if you use actual dates for the Jan Feb Mar
headers (format them as mmm-yy). The answer will require a fair amount
of IF this cell is greater than or equal to that cell, etc.

How will your spreadsheet expand to accommodate people who arrive on
December 31, 2005 and depart January 2, 2006?

 

[Guest]

At this point,the focus is only for the 2nd quarter of 2005 to determine costs.

Thanks for your assistance. I'll see "IF" i can make it work.

 

[Earl Kiosterud]

T,

If the start date is in A2, and the end date in B2, you could use:

=MAX(0, MIN(B2,DATEVALUE("2/28/05")) - MAX(DATEVALUE("2/1/2005"), A2)+1)

This gives the days, inclusive, for February. You'd have to manually change
the dates for other months. It can be made more general for other months,
but it gets more messy. It should work across years. If it comes up as a
date rather than a simple integer, remove the date formatting with either
Format - Cells - Number, or clear all formats (Edit - Clear - Formats).

The MIN gives either the end date, or then end of the month if the end date
is greater than (after) the end of the month. The inner MAX gives the start
date, or the beginning of the month if the start date is before (less than)
the beginning of the month. Then they're simply subtracted. The +1 makes
in inclusive, instead of a difference. The outer MAX is there for cases
where the both the start date and end date are before or after the month of
interest (Feb). In such cases, the difference comes up negative, so the MIN
is used to return 0.

 

[Biff]

Hi!

Start date in A1
End date in B1

Months listed as Jan, Feb, Mar etc in D1:O1 (covers all 12 months if you
want to do it that way)

Formula in D2:

=SUMPRODUCT(--(MONTH(ROW(INDIRECT($A1&":"&$B1)))=MATCH(D1,$D1:$O1,0)))

Copy across.

Assumes the year is not a necessary qualifier.

Biff

 

[Henry]

tesouthworthjr,

Try
In A1 your start date.
In B1 blank unless the end date is within that month when it will be the
end date.
In C1 IF (B1>"",B1,EOMONTH(A1,0)).
in D1 C1-A1 and format the cell as number with 0 decimal places.

Henry