+ Reply to Thread
Results 1 to 5 of 5

IF Statement - # of days in (date range 1) that fall within (date range 2)

  1. #1
    Registered User
    Join Date
    03-18-2011
    Location
    San Francisco, CA
    MS-Off Ver
    Excel 2007
    Posts
    3

    IF Statement - # of days in (date range 1) that fall within (date range 2)

    Hi there,

    I would like to find the # of days in a policy term that fall within a stated accounting period. The # of days will be used to calculate prepaid expense for the month.

    Example:

    Policy Term: 7/15/10-7/14/11
    Accounting Period: 7/1/10-7/31/10

    For this period, 17 days would fall in the period. For the last period (7/1/11-7/14/11, 14 days would fall in the accounting period. 100% of the days between months would be counted. Days occuring before or after the policy period date range would not be counted.

    Here is what I've been working on:

    HTML Code: 
    1	B	C	D	E	F
    2	P Begins	P Ends	T Begins	T Ends	Result
    3	6/1/10	6/30/10	7/15/10	7/14/11	0
    4	7/1/10	7/31/10	7/15/10	7/14/11	17
    5	6/1/11	6/30/11	7/15/10	7/14/11	30
    6	7/1/11	7/31/11	7/15/10	7/14/11	14
    7	8/31/11	8/31/11	7/15/10	7/14/11	0
    8					
    9	7/1/10	7/31/10	7/15/10	7/14/11	17
    10	8/1/10	8/31/10	7/15/10	7/14/11	0
    11	7/1/11	7/31/11	7/15/10	7/14/11	14
    12	8/1/10	8/31/10	7/15/10	7/14/11
    Trying to evaluate for row 12 result with the following nested IF statements with a warning that there are too many arguments:

    =IF(AND(D12>=B12,D12<C12),MAX(C12-D12+1,0),IF(E12>C12,0),IF(AND(E12<C12,E12>B12,C12>E12),E12-B12+1,C12-B12+1))

    I am including the excel workbook that I've been working on in the case that the above information is insufficient. It is entirely possible that I am making matters much too complicated, so your additions, deletions, corrections and suggestions are most welcome.

    Please forgive any newbie format issues (just this once)

    Thanks!
    Attached Files Attached Files
    Last edited by shaula; 03-18-2011 at 07:11 AM. Reason: Solved

  2. #2
    Valued Forum Contributor
    Join Date
    10-13-2010
    Location
    Sunderland, England
    MS-Off Ver
    Excel 2007 (Home) / 2003 (Work)
    Posts
    740

    Re: IF Statement - # of days in (date range 1) that fall within (date range 2)

    =IF(AND(D12>=B12,D12<C12),MAX(C12-D12+1,0),IF(E12>C12,0,IF(AND(E12<C12,E12>B12,C12>E12),E12-B12+1,C12-B12+1)))

    Does that work?

  3. #3
    Registered User
    Join Date
    03-18-2011
    Location
    San Francisco, CA
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: IF Statement - # of days in (date range 1) that fall within (date range 2)

    Ah, yes - it did.

    Now it seems I am experiencing a formula issue for the result desired in 5F (I'm getting a return of 0)

    Any ideas?

    Thank you!

  4. #4
    Valued Forum Contributor
    Join Date
    07-17-2005
    Location
    Abergavenny, Wales, UK
    MS-Off Ver
    XL2003, XL2007, XL2010, XL2013, XL2016
    Posts
    608

    Re: IF Statement - # of days in (date range 1) that fall within (date range 2)

    Hi

    I would enter the start date for your financial year in cell C1 on sheet Input

    Then in cell K8 enter
    =$C$3-$C$2+1-MAX(0,F8-$C$2)-MAX(0,$C$3-G8)

    in cell M8 enter
    =$C$3-$C$1+1-MAX(0,F8-$C$1)-MAX(0,$C$3-G8)
    --
    Regards
    Roger Govier
    Microsoft Excel MVP

  5. #5
    Registered User
    Join Date
    03-18-2011
    Location
    San Francisco, CA
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: IF Statement - # of days in (date range 1) that fall within (date range 2)

    Wonderful, thank you!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1