date intervals

G

Guest

oops!! for that first post
Dear all
Some help please. I have 2 dates a start date and an end date and I have to
make payments either quarterly or semi annually between the 2 dates, e.g.,
start date = Jan 15 2006 end date = July 15 2015 the number of quarterly
payments that I would have to make between the 2 dates is 37 and the number
of semi annual payments is 19. Is there a formula that could give me these
results
 
B

Bob Phillips

Quarters

=SUMPRODUCT(--(DATE(YEAR(A1),MONTH(A1)+ROW(A1:A100)*3,DAY(A1))<A2))

Semi-annual

=SUMPRODUCT(--(DATE(YEAR(A1),MONTH(A1)+ROW(A1:A100)*6,DAY(A1))<A2))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
B

Bob Phillips

Forgot to mention that the start and end dates were in A1 and A2
respectively, but I guess that you figured that <vbg>

Bob
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Update cell value based on name and specific date 1
Frequency of Billing 2
Excel Help with dates 2
Table/query layout trouble 2
Problem with NetworkDays ... Inconsistent results 0
return latest date in a record from a set of fields 4
table lookup 5
date range within a date range 2

Top