Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
adding occurrences for date range | Excel Discussion (Misc queries) | |||
Where is DateDiff function in Excel 2002 ? | Excel Worksheet Functions | |||
unwanted number to date conversion while pasting data from web | Excel Worksheet Functions | |||
Calculating days between current date and a date in future NETWORKDAYS() function | Excel Worksheet Functions | |||
I have a list of dates that I need to count based on a date range | Excel Worksheet Functions |