Hi
"Biff" wrote in message
...
Nice!
Just a little nit pick.....
In the MAX functions (both of them) you're referencing A2 twice:
MAX(A$2,DATE(YEAR(A$2),MONTH(A$2)+ROW()-2,1),A$2)
Obviously I forget 1st reference to A2, when I was through with designing
DATE-part. Thanks for correction!
=IF(MAX(A$2,DATE(YEAR(A$2),MONTH(A$2)+ROW()-2,1))<B$2,MIN(DATE(YEAR(A$2),MONTH(A$2)+ROW()-1,0),B$2)-MAX(A$2,DATE(YEAR(A$2),MONTH(A$2)+ROW()-2,1))+1,"")
will be right then. A bit shorter too.
--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )
Biff
"Arvi Laanemets" wrote in message
...
Hi
Let's assume you have start day in cell A2, and end day in B2. Into range
D1:E1 you enter "Month" and "Days". Into cell D2 you enter the formula
=IF(DATE(YEAR(A$2),MONTH(A$2)+ROW()-2,1)<B$2,TEXT(DATE(YEAR(A$2),MONTH(A$2)+ROW()-2,1),"mmmm"),"")
Into cell E2 you enter the formula
=IF(MAX(A$2,DATE(YEAR(A$2),MONTH(A$2)+ROW()-2,1),A$2)<B$2,MIN(DATE(YEAR(A$2),MONTH(A$2)+ROW()-1,0),B$2)-MAX(A$2,DATE(YEAR(A$2),MONTH(A$2)+ROW()-2,1),A$2)+1,"")
Now copy both formulas down for at least for same number of rows as
you'll have months in longest date interval. It's done!
--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )
"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
|