Hi!
I would want it to break it down like this:
Feb 13
March 31
April 25
I get:
Feb 14
March 31
April 25
You have to include the year in your dates! How many years will this span?
Here's how to do it for a calander year from Jan 1 to Dec 31:
A1 = start date 2/15/2005
B1 = end date 4/25/2005
A3 = 1/1/2005
A4 = formula copied down to A14:
=DATE(YEAR(A3),MONTH(A3)+1,1)
Enter this formula in B3 and copy down to B14:
=SUMPRODUCT(--(ROW(INDIRECT(A3&":"&EOMONTH(A3,0)))=A$1),--(ROW(INDIRECT(A3&":"&EOMONTH(A3,0)))<=B$1))
Biff
"Rayco" wrote in message
...
Hi,
I've posted this twice, and there has been no reply. Either no one knows
the answer, or no one understnads my question?
I am trying to calculate the occurrence of an activity during a given
month
within a period.
For example:
Let's say that Jan came to visit from Feb 15 to April 25.
How can I get Excel to calculate how many days she stayed during the
months
of Feb, March, and April?
I would want it to break it down like this:
Feb 13
March 31
April 25
Also, an add-on for calculating random dates.... where I can input March
31
and April 10 and get the results:
March 1
April 10
I know that it can be done, since I ended up with a horrific formula with
if's and then's for each month, that I had to repeat for each month
(column). However, it is very hard to work with.
Is there an easier way?
Thanks for your help!
Rayco
|