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

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

The EOMONTH function requires that the Analysis ToolPak add-in be installed.
If it's not you can use this:

=SUMPRODUCT(--(ROW(INDIRECT(A3&":"&DATE(YEAR(A3),MONTH(A3)+1,0)) )=A$1),--(ROW(INDIRECT(A3&":"&DATE(YEAR(A3),MONTH(A3)+1,0)) )<=B$1))

Biff

"Biff" wrote in message
...
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

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

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)

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

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

Thanks!

Works just fine!


"Biff" wrote in message
...
The EOMONTH function requires that the Analysis ToolPak add-in be
installed.
If it's not you can use this:


=SUMPRODUCT(--(ROW(INDIRECT(A3&":"&DATE(YEAR(A3),MONTH(A3)+1,0)) )=A$1),--(R
OW(INDIRECT(A3&":"&DATE(YEAR(A3),MONTH(A3)+1,0)))< =B$1))

Biff

"Biff" wrote in message
...
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