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
?        ?         ?
0
7/14/2005 4:27:04 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
259 Views

Similar Articles

[PageSpeed] 47

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?

0
CycleZen (674)
7/14/2005 6:12:44 PM
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?

0
CycleZen (674)
7/14/2005 6:13:01 PM
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?
> 
> 
0
7/14/2005 6:22:04 PM
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" <tesouthworthjr@discussions.microsoft.com> wrote in message 
news:31D70A8E-DC6A-4CBD-AFAF-FCFDD979980A@microsoft.com...
>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
> ?        ?         ? 


0
someone798 (944)
7/14/2005 6:25:12 PM
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

"tesouthworthjr" <tesouthworthjr@discussions.microsoft.com> wrote in message 
news:31D70A8E-DC6A-4CBD-AFAF-FCFDD979980A@microsoft.com...
>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
> ?        ?         ? 


0
biffinpitt (3172)
7/14/2005 7:25:59 PM
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" <tesouthworthjr@discussions.microsoft.com> wrote in message 
news:31D70A8E-DC6A-4CBD-AFAF-FCFDD979980A@microsoft.com...
>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
> ?        ?         ? 


0
7/14/2005 8:45:02 PM
Reply:

Similar Artilces:

Can I change the calendar year beginning date?
Our fiscal year runs July 1 thru June 30 of each year. I need to track attendance, gas cards, etc for monthly reports. I have a HUGE table with a gazillion queries and reports for each one. I need one report to reflect quarterly output for our fiscal year as stated above. Can I make this report do that? Thank you! Create a query to use as the source for your report. In query design, type this into the Field row: FinYear: DateAdd("m", -6, [InvoiceDate]) replacing InvoiceDate with the name of your date field. This yields 2007 for all dates in the 2007/2008 financial year...

Date Formatting when Concantenating
I have a simple question. I have a cell that has date that looks lik this: 10/15/1999 14:34 When I use the concantenate feature my date looks like this: 36448.6073611111 I tried to format the call every which way - but I cannot get it t look the original. Feeling really silly for even asking.. thanks all for your help -- Message posted from http://www.ExcelForum.com Hi bleu808! Use: ="Today is "&TEXT(TODAY(),"mm/dd/yyyy hh:mm") -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au "bleu808 >" <<bleu808.189yij...

Show a date 30 days out
I have a feild that I would like to show what the date would be in 30 days for a feild with a date in it [Sold Date]. Sold Date feild would be 1 Jan 07 so the feild would show 30 Days later 31 Jan 07. I did =[Sold date]+30 and it did not work Thanks how about dateadd: dateadd("d", 30, [Sold Date]) -- steve. "KAnoe" <KAnoe@discussions.microsoft.com> wrote in message news:DE9BDDAE-5C84-4A52-8185-DAB6C3167E7A@microsoft.com... >I have a feild that I would like to show what the date would be in 30 days > for a feild with a date in it [Sold Date]. > ...

How OMPM Scanner (offscan) Filter by Access/Modified Date ?
Hello, I have problem to inventory excel files on very big file server, but I believe there are so many documents we no longer need to maintain. I want to skip files if the access date or modified date longer than 6 month, but don't see the OMPM providing feature about it. I currently running OMPM since 2 weeks ago and running out of time for reporting to my manager. Please help me, this is my critical assignment. -- Eldi Munggaran ...

Dates in fomula showing as whole number
I have a fomula in a cell that takes the name of a person (from cell 2B), their License number (from another cell 2C)and the Date that License Expires (From cell 2D). The expire date in "2D" is either the word "none" or a date that that persons license needs to be renewed. Those instructors with "None" come out in the calculated field fine, however the ones with dates come back as whole numbers, Example 8/6/10 shows 40396. any help will be appreciated Hi, You need to change the format of that cell or column, highlight the cell or the column, right click o...

auto number index snafu
I have a 2003 DB I've been adding to for 2 years. It's at 423 records = now. I export to a PDF for simpler searching and viewing. Using the search the other day, I went to grab DVD 186, on carousel tower= 2. And what came up didn't match the record. The index was totally screwed. I went thru all 423 records and created a numeric index by hand. The auto number field is useless. But I would still prefer an auto number field. Trouble is when I create the auto number field, it's still off the actual numbers of the records.. ie: 99-100 is actually 99-186,187, 188, 189 100-385,...

Sequential ticket numbers and printing 4 to a page
Hi, i have mail merged my numbers from excel into my publisher ticket. Thanks that worked well, now i want to print 4 tickets to an A4 page and in print preview, i get 4 tickets all with the same number on one page. how do i get around this? Many thanks Print preview showing all the same is a bug. Print a test page. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com http://officebeta.iponet.net/en-us/publisher/FX100649111033.aspx "Neyol" <Neyol@discussions.microsoft.com> wrote in message news:90034D4C-9951-4550-9DAC...

Line Chart with dates in 5 day working week only
Hi, Trying to format a chart so that only the 5 working days of the week are displayed on the x axis. The source data only has the five days (e.g. 05/09/2005 down to 09/09/2005 and then on to 12/09/2005 down to 16/09/2005 etc etc) So I have missed out the weekend dates. When I create the line chart however, the weekend dates appear automatically and just show no point on the chart, therefore there is a longer line between Fridays and Mondays!! Hope this makes sense. Does anyone have any ideas on how to change this? I have tried looking at Tools-options-chart and cannot seem to turn...

Auto assign Lot Numbers in PO receiving
Hi! Has anyone made a customization to auto assign Lot numbers upon item receipt? My client's lot number is numeric, they want the system to auto assign this. Any ideas? Thanks! -- Marisol Mortera Marisol, Yes indeed, I created one for a client. You probably will not be able to use it as-is because of some client specific functionality but I'm sure you could use this as a stepping stone. The key features were that it would allow for rapid lot number entry by a. Auto-incrementing the lot number - we had Alphanumeric lot numbers so we added a suffix and b. copy the previous lo...

How to get only the year in the date format in Access
How to get only the year in the date format I.e in the table in need to display only year E.g 2005 - should be display " 05" automatically Custom format the cell as: yy -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "yanu" <yanu@discussions.microsoft.com> wrote in message news:14CE9F60-F7B9-467A-8C16-71088C31BEBA@microsoft.com... > How to get only the year in the date form...

Ordering by number and text
I use a report to print out checklists that in the detail section have item number to delineate each checklist item. The item numbers as an example are 1-1, 1-2, 1-2a, 1-2b, or 2-1, 2-2, 2-3, 2-3a, 2-3b and so on. The report is grouped by checklist section and the grouping works perfectly but when the report is printed the item numbers are ordered in this order 1-1, 1-10, 1-11, 1-2, 1-3, 1-3a, 1-3b, 1-4, 1-5, 1-6, 1-7, 1-9. Obviously I want the the order to be in proper numerical sequence where 1-10 comes after 1-9. but even replacing the '-' with a decimal point doesn't ...

date/time formatting
I download a csv report from a web based program. Everything works great but the date comes through as "Jul 21 2009 1:51pm". I do not need the time in my report. I have tried reformatting the cells, tried to copy to a new file with the cells already formatted to "7/21/09", opening the cell format using the F2 key and deleting the time (time will still not be formatted correctly). The only way I get get just the time is to retype every cell. What am I doing wrong? I am using Excel 2002 SP3 TIA, Cindy When data is downloaded from the web, a lot of "other&quo...

matching columns of numbers
In EXCEL 2000 for Windows, I have two columns of numbers. Column A has 500 numbers, Column B has 1000 numbers. I need to know which cells in Column A have a match in Column B, and if so, what is the Cell (or row number) in B that matches to that particular cell in A. How can I do this? Thank you for your help. ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Business Software Galore! Free Excel Forum http://www.ozgrid.com/forum *** Hi try the following: - insert a new column between A and B (so make B the new C column) enter the following in B1 =IF(ISNA(MATCH...

Zero filling a number field
I have an auto number field that I want to zero fill to six digits but can't figure out how. Any ideas? You don''t say where you're trying to do this, but basically Format(YourAutoNumber, "000000") pdlginternet@aol.com wrote: >I have an auto number field that I want to zero fill to six digits but >can't figure out how. Any ideas? -- There's ALWAYS more than one way to skin a cat! Answers/posts based on Access 2000/2003 Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200711/1 You can't do that wi...

How do I change numbers to negative without re-typing?
I have a large range of data that needs to be changed to negative numbers, Can I do this in Excel? ...

Determine number of rows with data
Hi I am using the macro below to pull some data from an external workbook. The 2 issues I need to sort are: 1. The number of rows in the external workbook can vary. How do I amend this code to pull all of the rows with data? 2. The number of rows in the autofill also may vary. How do I autofill only the number of rows required? i.e the number of rows in column A that contain data. 'Lookup Previous Month Sales Columns("K:K").Select Selection.NumberFormat = "General" Range("K4").Select Selection.FormulaArray = _ "=S...

Copy/paste range of rows between 2 dates...
Hi! I have a sheet called data which act as a database. The column A has the dates. In order to create customized chart in a userform, for different range of data(i.e from column D, G and M...), I'd like to select a range of rows that are between 2 dates and create the charts accordingly. Or copy to range to another sheet and then create the charts. I am not so advanced in VBA and any help would be greatly appreciated. Thanks! Greg ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Business Software Galore! Free Excel Forum http://www.ozgrid.com/forum *** Hi ...

Converting number to text
Is it possible to convert, say 1234 to one thousand two hundred and thirty four Thanks There is no direct functions to convert this. For a VBA solution check out the below links http://www.ozgrid.com/VBA/ValueToWords.htm http://support.microsoft.com/kb/213360 http://www.xldynamic.com/source/xld.xlFAQ0004.html -- Jacob (MVP - Excel) "booshi" wrote: > Is it possible to convert, say 1234 to one thousand two hundred and thirty > four > > > Thanks > > > . > ...

Sequentially number lines automatically
Version: v.X Operating System: Mac OS X 10.5 (Leopard) Processor: Intel How can automatically number the lines as I enter data. <br><br>so box A1 would automatically be 1 <br> and the next time I created an entry on another line that line would be 2, etc. <br><br>The reason is so that I can sort, delete and the numbers will remain, so I can also go back to the order than things were entered into. <br><br>Could do this manually, but a pain. <br><br>Or could possibly do this with a date and time? <br><br>Thanks Steevee Se...

Excel Number Format Codes
I can't figure out how to format numbers so that when you type 3220 it will look like 32.20 kinda like how you can enter numbers on an adding machine. I would greatly appreciate this number format code. Thank you. -- 1:~ Hi, I think you want to go Tools | Options | Edit | Fixed Decimals - 2. That will divide all the numbers you enter by 100. You will also have to format the row or column to display 2 decimals. Select the cells or range, right mouse click, format cells, numbers, 2 decimals. Hope that helps. Best regards, Kevin "MBB" <MBB@discussions.microsoft.com> w...

Receivings Number
Hi, When I am doing my purchase receiving at the Receivings Entry, I sometimes encounter some problem which the Receivings Number. For example, suppose the new receivings number should be REC/03/000004, but this number REC/03/000005 is given to me instead. What could be the possible cause of the missing receivings number:REC/03/000004. (this no. is not used by anyone). Please advise or tell me how can I avoid. Thanks in advance. I believe it's when someone started to do a receivings but then deleted it. It already increments to the next number. You can prevent deleting of documen...

Altering the range that is plotted by a chart via VBA
Good morning all. I haven't really done a huge amount on charting, so I'm sure you'll think that these questions are a bit simplistic, but here goes! I plot a chart from a worksheet that contains three columns of data - the X axis contains week numbers, and is in B29:B54. Actual hours are entered in F29:F24 and cumualtive hours in G29:G24. The cumulative formula is of the form: =SUM($F$29:F29) So, each week, in order to plot another week's data, I have to copy this cumulative formula down by one cell, which, considering I have a lot of these reports, is a bit time cons...

Calculating dates #3
Can anyone tell me what I should use (in the way of helper cells) to take any date (mm/dd/yyyy) and turn it into that same month and day for specific year? For instance, turn... 10/12/2009 into 10/12/2010 and 4/6/1998 into 4/6/2010 I'm trying to determine anniversary date based on start date and do it starting in 2010. TIA -- Jordon Try this: =3DDATE(2010,MONTH(A1),DAY(A1)) Assuming your date is in A1. Hope this helps. Pete On Jan 5, 5:35=A0pm, Jordon <jordon@REMOVE~THISmyrealbox.com> wrote: > Can anyone tell me what I should use (in the way of helper cells) > to tak...

how do I format cells to change date and time to just date
I want to format a column that contains date and time and I want it to show just the date and not the time. Going into format and clicking on the date and changing doesnt work. Probably the cells aren't really dates, but text. You can check with the ISTEXT() function. If they are text, formatting doesn't have any effect. -- Kind regards, Niek Otten Microsoft MVP - Excel "bondam" <bondam@discussions.microsoft.com> wrote in message news:F4D61EC1-30E2-4723-89E1-F47B545818EE@microsoft.com... >I want to format a column that contains date and time and I want it t...

Limit number of characters in a cell
Hi, Is there a way to limit the number of characters that a user can key into a cell. I want to get a list of names and addresses imputted by user, but I don't want them to be more than 35 characters. Thanks for the help Dr. Senji Take a look at Data|Validation. You can have excel yell at the user when they hit enter after typing in a too-long string. Dr Senji wrote: > > Hi, > > Is there a way to limit the number of characters that a user can key into a > cell. > > I want to get a list of names and addresses imputted by user, but I don't > want the...