On 29/04/2012 5:56 PM, joeu2004 wrote:
"Stephen" wrote:
I made my query too complicated for me to understand, even
though you worked it out. Could I try an easier test please?

All I want to do now is count the number of days that fall
inside one tax year.
[....]
If the END date was say 05/07/2009, I would expect the answer to be 362
(03/07/2008 START date till 30/06/2009). In the next cell along, when I
copy the same formula from E8 to E9, the answer would be 5 (01/07/2009
till 05/07/2009 END DATE)

I suspect your only difficulty with the original formula is the lack of
judicious use of absolute references [1]. That would make it easy to copy
the formulas across.

Download the following example from
http://www.box.com/s/ce7f818b372ee4ae734c.

Unfortunately, this forum does not permit to show a screen shot of the
example [2]. The following might not be formatted correctly.

(Caveat: I use the date format m/d/yyyy; you use d/m/yyyy. The format does
not matter since I use DATE and EDATE to express dates.)

A B C D E F
1 tax years
2 start date 7/3/2008 7/1/2007 7/1/2008 7/1/2009 7/1/2010
3 end date 7/5/2009 6/30/2008 6/30/2009 6/30/2010 6/30/2011
4 total days 368
5 total amt $450.00
6 days per tax yr 0 363 5 0
7 prorated amt $0.00 $443.89 $6.11 $0.00

The formula in B4 is:
=B3-B2+1

The formula in C6 is:
=MAX(0,MIN($B$3,C$3)-MAX($B$2,C$2)+1)

The formula in C7 is:
=ROUND($B$5*MAX(0,MIN($B$3,C$3)-MAX($B$2,C$2)+1)/$B$4,2)

Because you want to copy C7 across several tax years, it is easier not to
bother with avoiding rounding errors, as I did before. It can be done; but
it might require different formulas in some cells.

C6 can be copied across into D6 through F6.

C7 can be copied across into D7 through F7.

Ostensibly, the number of days is endDate minus startDate plus 1. See the
formula in B4.

MIN($B$3,C$3) selects the end date within that tax year. It is the contract
end date (B3) or the end of the tax year (C3), whichever is earlier.

MAX($B$2,C$2) selects the start date within that tax year. It is the
contract start date (B2) or the start of the tax year (C2), whichever is
later.

MAX(0,...) covers the case when the contract end date is before the
start of
the tax year or the contract start date is after the end of the tax year.
In that case, "endDate minus startDate plus 1" would be negative.
MAX(0,...) forces the negative result to be zero.


-----
[1] The original formulas might be written as follows:

In AE10:
=IF(COUNT($C$9,$D$10,$AA$9,AE$3:AF$4)<7,"",
ROUND($AA$9*MAX(0,MIN($D$10,AE$4)-MAX($C$9,AE$3)+1)/($D$10-$C$9+1),2))

In AF10:
=IF(AE10="","",IF(AND(AE$3<=$C$9,$D$10<=AF$4),ROUN D($AA$9-AE10,2),
ROUND($AA$9*MAX(0,MIN($D$10,AF$4)-MAX($C$9,AF$3)+1)/($D$10-$C$9+1),2)))


[2] For future questions, I suggest that you post in the Excel Answers
Forum
at http://answers.microsoft.com/en-us/office/forum/excel. The "rich text"
interface (GUI) allows us to paste screen shots, which might help you to
visualize what we are talking about.


joeu2004,

You are a genius!

The spreadsheet worked out exactly as desired, and when I change my
START and END dates, it all works fine!

Thanks for the explanation too - not sure I fully understand it all, but
the formulas work, and that's the main thing!

Thanks heaps,

Stephen