Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
tesouthworthjr
 
Posts: n/a
Default Number of Days in a Date Range

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
? ? ?
  #2   Report Post  
Dave O
 
Posts: n/a
Default

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?

  #3   Report Post  
Dave O
 
Posts: n/a
Default

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?

  #4   Report Post  
tesouthworthjr
 
Posts: n/a
Default

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.

"Dave O" wrote:

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?


  #5   Report Post  
Earl Kiosterud
 
Posts: n/a
Default

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.
--
Earl Kiosterud
www.smokeylake.com

"tesouthworthjr" wrote in message
...
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
? ? ?





  #6   Report Post  
Biff
 
Posts: n/a
Default

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:$O 1,0)))

Copy across.

Assumes the year is not a necessary qualifier.

Biff

"tesouthworthjr" wrote in message
...
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
? ? ?



  #7   Report Post  
Henry
 
Posts: n/a
Default

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

"tesouthworthjr" wrote in message
...
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
? ? ?



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
adding occurrences for date range Mike Excel Discussion (Misc queries) 0 April 8th 05 11:33 PM
Where is DateDiff function in Excel 2002 ? Nigel Welch Excel Worksheet Functions 4 March 4th 05 04:18 PM
unwanted number to date conversion while pasting data from web Jacek Excel Worksheet Functions 1 February 24th 05 03:59 PM
Calculating days between current date and a date in future NETWORKDAYS() function Faheem Khan Excel Worksheet Functions 2 February 10th 05 08:18 PM
I have a list of dates that I need to count based on a date range ejb030353 Excel Worksheet Functions 4 November 24th 04 03:27 PM


All times are GMT +1. The time now is 03:42 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"