Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    South Carolina, USA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date count (Excel 97)

    Is there a command in Excel that will tell me the number of days within a month for a date range? Example: Date Admitted: 10/8/01 Date released: 12/13/01
    I want to know how many days in Oct = 22, Nov = 30, Dec = 13 that this person was under our care per month.

    Any suggestions???

    Thanks,
    Deborah

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Date count (Excel 97)

    Deborah, see the attachment which uses the =DATEDIF(,,) function to show:

    1. The number of stay days in the admit month
    2. The number of stay days in the discharge month if the discharge month is not the admit month
    3. The total number of stay days in the intermediate months between admit and discharge dates if any.
    4. The total number of days stay.
    5. A check to see that the count is internally correct.

    I did not test this beyond the four sample dates shown , so please conduct your own accuracy tests to ensure I haven't missed anything.

    If you need to break out each intermediate month, things will need to be more complex; all I have done here is add a line for the total number of stay days in the intermediate months between the admit and discharge month, not a list of stay days by each intermediate month.

    The attachment also provides Chip Pearson's documentation of the =DATEDIF(,,) Function and a link to his great website.

    HTH.
    Attached Files Attached Files
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Date count (Excel 97)

    There is no specific command (function) for what you want, but a series of formulae should work, one for each month under consideration.

    If the start date is in B2, the end date in C2, and the first of Jan in E1, the following formula will th enumber of days for January. The formula can then be copied for each of the other months + 1.

    =IF(F$1-1<$B2,0,DATEDIF(MAX(E$1,$B2),MIN(F$1-1,$C2),"d")+1)

    A worksheet is attached which might help see what I am doing, and maybe somebody will come up with a clever way of doing the same thing.

    Andrew C
    Attached Files Attached Files

  4. #4
    Star Lounger
    Join Date
    Feb 2001
    Location
    South Carolina, USA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date count (Excel 97)

    Andrew,

    I entered the formula and it works great. There is one problem, when the date is greater than the end date, I get a #num! error message. I have tried to make some changes but to not avail. Can you provide some suggestions?? I am using dates through the end of the year - I would like a 0 in the fields instead of the #num! message.

    Thanks,
    Deborah

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Date count (Excel 97)

    Can you explain what you mean by "when the date is greater than the end date"? There are only admit and discharge dates, Andrew calls them In and Out.

    BTW, Andrew, your formulas do not return correct results for stays which begin in one year and are discharged next year, such as an In of 12/20/01 and an Out of 1/10/02. But your layout is better than my approach.
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    Star Lounger
    Join Date
    Feb 2001
    Location
    South Carolina, USA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date count (Excel 97)

    I was referring to the date at the top such as Feb01 and then the out date. I have read in the Pearson Software web site that the formula will return the #num! if the month (feb01) is greater than the out date. I should be able to add something to this formula to produce an 0 or the words "out of range" but what ????

    Thanks,
    Deborah

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Date count (Excel 97)

    Andrew (where are you, Andrew?) has used the "d" argument to =DATEDIF(), so the only time the formula should error out is if the out date is chronologically the same day as or before the in date, which can't happen in reality, though it could be entered in error.

    You could handle this by surrounding the existing formulas with:

    =IF(C2>B2,existing_formula,"Invalid Dates")

    (I'll try to work on this in more depth later.)
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Date count (Excel 97)

    Deborah, you are quite correct - sorry. The following should fix your problem<pre>=IF(MAX(E$1,$B2)>MIN(F$1-1,$C2),"",DATEDIF(MAX(E$1,$B2),MIN(F$1-1,$C2),"d")+1)</pre>

    Sample attached. For clarity if a given month has no value I have left it blank. If ypu want to use 0, then replace "" with 0 in the formula.

    John, the formula should work across years but I probably did not make it clear enough that it is dependant on the top row extending at least one month beyond the discharge date.

    Andrew C

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Date count (Excel 97)

    Sorry, forgot to re-attach the file after the Preview.

    Here it is
    Attached Files Attached Files

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Date count (Excel 97)

    Oh, I see, there have to be as many month columns as months of stay. <img src=/S/doh.gif border=0 alt=doh width=15 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

  11. #11
    Star Lounger
    Join Date
    Feb 2001
    Location
    South Carolina, USA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date count (Excel 97)

    Works Great!!!

    THANKS.....

    Deborah

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •