Days Within Months

[Rayco]

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

 

[Biff]

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

 

[Biff]

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

 

[Arvi Laanemets]

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!

 

[Biff]

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]

Hi

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.

 

[Rayco]

Thanks!

Works just fine!

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)) =SUMPRODUCT(--(ROW(INDIRECT(A3&":"&EOMONTH(A3,0)))>=A$1),--(ROW(INDIRECT(A3&
":"&EOMONTH(A3,0)))<=B$1))