Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Apr 2001
    Location
    Washington, Washington, USA
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formula to count days within a range within a rang (2000/SP1)

    I'm working on modifying a spreadsheet created by someone else. I admit--readily--that formulas are not my strong point, and I don't work with them a lot. So I guess I should be satisfied that I at least figured out what this one is doing (which took awhile!). But it just seems like the hard way to calculate what should be simple (I would think): given a start date and end date, how many days in a particular month fall within that range? Below is the original formula and what I built to help me figure it out.

    =IF($E9>J$7,0,IF($F9<J$6,0,IF(AND($E9<=J$6,$F9>=J$ 7),J$7-(J$6-1),IF(AND($E9>=J$6,$F9<=J$7),$F9-($E9-1),IF(AND($E9<=J$6,$F9<=J$7),$F9-(J$6-1),IF(AND($E9>=J$6,$F9>=J$7),J$7-($E9-1),0))))))*$H9

    E9=StartDate
    F9=EndDate
    J6=MonthStart
    J7=MonthEnd

    Question the formula answers: how many days in this month fall within the start and end dates for this task? (which it then multiplies by dollars per day.)

    IF THE TASK DOESN

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula to count days within a range within a rang (2000/SP1)

    This formula does the same, but takes up less real estate:

    =MAX(MIN(F9,J7)-MAX(E9,J6)+1,0)*H9

    Explanation:

    <span style="background-color: #FFFF00; color: #000000; font-weight: bold">MAX(E9,J6)</span hi> returns the later of the StartDate and the MonthStart, i.e. the start of the overlap of the range and the month.
    <span style="background-color: #FFFF00; color: #000000; font-weight: bold">MIN(F9,J7)</span hi> returns the earlier of the EndDate and the MonthEnd, i.e. the end of the overlap of the range and the month.
    <span style="background-color: #FFFF00; color: #000000; font-weight: bold">MIN(F9,J7)-MAX(E9,J6)</span hi> returns the number of days between the start and the end of the overlap.
    <span style="background-color: #FFFF00; color: #000000; font-weight: bold">MIN(F9,J7)-MAX(E9,J6)+1</span hi> adds one to include the first (or last) day. If this number is negative, the EndDate is before the StartDate, or there is no overlap with the chosen month.
    <span style="background-color: #FFFF00; color: #000000; font-weight: bold">MAX(MIN(F9,J7)-MAX(E9,J6)+1,0)</span hi> reduces the result to 0 if it is negative.
    <span style="background-color: #FFFF00; color: #000000; font-weight: bold">MAX(MIN(F9,J7)-MAX(E9,J6)+1,0)*H9</span hi> multiplies by the daily rate.

    If you want the cell references to be absolute, add $ characters.

  3. #3
    Star Lounger
    Join Date
    Apr 2001
    Location
    Washington, Washington, USA
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula to count days within a range within a rang (2000/SP1)

    Thanks, Hans! That's exactly what I was hoping for, something that does the same thing with less effort, so to speak. It seemed like such a simple calculation, I couldn't imagine that the solution used by the creator of this workbook was the only approach to the problem. But I didn't have a clue where to start differently. In this new job (I've been there three months so far), I'm going to have the opportunity to work with Excel much more than in the past. I'm hoping that means I'll become as comfortable in the language of formulas as I am navigating around in a Word document via code.

    Now, if you could just shed the same light on my Excel outlining problem (posted in the VBA area). I still can't even wrap my mind around it enough to describe the process in English, let alone write the code to make it happen. Of course, that might have something to do with the fact that I worked so many hours this weekend, that I'm only about seven hours away from calling it a week! :-) The good news is they extended my deadline. I sat sat down this morning with the Project Manager and we plotted it all out in MS Project. Ended up finishing sometime in September. So I left saying, "No WONDER I couldn't get it all done this weekend!"

    Again, many, many thanks! You've come to my rescue many times, and I truly appreciate your willingness to share your knowledge.

    --Karyl

Posting Permissions

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