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

1. 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!

2. 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. 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. 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)

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

Wonderful, thank you!

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

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