How to determine Excel values for date ranges inside of date ranges

[JVFriederick]

In Excel I am trying to summarize the total value for a set of data based upon date ranges. The data consists of various "effective dates" that indicate when an item changed status (P1, P2, P3, etc). An item cannot repeat the date of status change, however the status can jump from any level to any other level (there is no orderly progression).

The effective dates are as follows :

TABLE/RANGE #1
ITEM P1...... P2...... P3...... P4......
1000 09/01/06 08/15/07 10/25/07 02/01/08
1001 02/01/08 08/18/06 02/25/07 12/12/07
1002 06/11/04 08/25/07 12/25/06 02/07/07
1003 07/21/06 08/05/06 03/03/07 03/09/08

The value while in each level is as follows :

TABLE/RANGE #2
ITEM P1 P2 P3 P4
VALUE 10 15 25 40

I wish to determine the total value of each ITEM when the DATE_BEGIN >= 01/01/07 and DATE_END <= 03/31/07

There are a total of 90 days for this example date range, if an item was classified as P1 the entire time, the total value would be 90*10=900. If an item was P1 for 15 days, P3 for 50 days, and P4 for 25 days, the total value would be 15*10 + 50*25 + 25*40 = 2400

I would prefer a formula based solution, however, VBA is fine if it does the trick. At this point I would welcome any ideas since I am hitting a wall.

I am hoping that others have solved this type of problem?

 

[Gavona]

Much easier if you reconfigure your data:

PeriodStart	01 Jan 07						
PeriodEnd	31 Mar 07						
							
Item	Status	StartDate	EndDate	P1	P2	P3	P4
1000	P1	01 Sep 06	15 Aug 07	90	0	0	0
1000	P2	15 Aug 07	25 Oct 07	0	0	0	0
1000	P3	25 Oct 07	01 Feb 08	0	0	0	0
1000	P4	01 Feb 08	03 May 08	0	0	0	0
1001	P2	18 Aug 06	25 Feb 07	0	56	0	0
1001	P3	25 Feb 07	12 Dec 07	0	0	35	0
1001	P4	12 Dec 07	01 Feb 08	0	0	0	0
1001	P1	01 Feb 08	03 May 08	0	0	0	0
1002	P1	11 Jun 04	25 Dec 06	0	0	0	0
1002	P3	25 Dec 06	07 Feb 07	0	0	38	0
1002	P4	07 Feb 07	25 Aug 07	0	0	0	53
1002	P2	25 Aug 07	03 May 08	0	0	0	0
1003	P1	21 Jul 06	05 Aug 06	0	0	0	0
1003	P2	05 Aug 06	03 Mar 07	0	62	0	0
1003	P3	03 Mar 07	09 Mar 08	0	0	29	0
1003	P4	09 Mar 08	03 May 08	0	0	0	0

First three columns are your original data re-configured and then sorted by Item, StartDate

Formula in D5 (EndDate)
=IF(A6=A5,C6,TODAY())
Copied down

Formula in E5 (P1) =IF(AND($C5<PeriodEnd,$D5>PeriodStart,$B5=E$4),MIN($D5,PeriodEnd)-MAX(PeriodStart,$C5)+1,0)
Copied down and across

Sorry, just realised I have changed the display format for your dates - this makes no difference to the solution. I assume you are using proper dates not text that looks like dates.

Gavin

 

[JVFriederick]

Thanks for the idea, if I could re-arrange the data for analysis I would use it.

Unfortunately, the active data is contained within a range that each record represents a single family enrolled in a program, so I cannot rearrange the dates vertically to indicate date periods. I must maintain the rows of data in place.

At this point I have used the LARGE function to determine the sequence of dates (most recent to oldest) and now I need a way to pick dates between other dates.

I appreciate your response, and hope that someone else can help me figure this out.

 

[SkipVought]

"Unfortunately, the active data is contained within a range that each record represents a single family enrolled in a program, so I cannot rearrange the dates vertically to indicate date periods."

That is a false argument. So you include a column representing the single family enrolled in a program. Having your data in this format which is counter-productive for data analysis and data reporting (summarizing, etc), you shoot yourself in the foot, and tie a hand behind your back. In doing so, you defeat the functionality of Excel's plethora of data analysis, lookup and reporting features.

As one surgeon said to the other, "Suture self!"

Skip,

_{Just got a nuance...
to replace the old subtlety!}

 

[Salut39]

TRY THIS SEE IF THAT'S HELP.

TABLE/RANGE #1 01/01/07 31/03/07
ITEM P1 P2 P3 P4
1000 01/09/06 15/08/07 25/10/07 01/02/08
1001 01/02/08 18/08/06 25/02/07 12/12/07
1002 11/06/04 25/08/07 25/12/06 07/02/07
1003 21/07/06 05/08/06 03/03/07 09/03/08

The value while in each level is as follows :

TABLE/RANGE #2
ITEM P1 P2 P3 P4
VALUE 10 15 25 40

14 - - - -
15 - - 850 -
16 - - - 2,080
17 - - 700 -

B14 =IF(AND(B3>$C$1,B3<$D$1),($D$1-B3)*B$12,0)

 

[JVFriederick]

Salut39 -
I will give this a try this evening.... thanks.

 

[Gavona]

You could easily reconfigure your data.

The best way is described here faq68-5287. Works perfectly for your data. (Thanks to Skip for this FAQ)

The cludgy way is to write formulae on a new workbook to get this. Formulae from the first row would copy down.

1000 P1 01 Sep 06 1000 P2 15 Aug 07 1000 P3 25 Oct 07 1000 P4 01 Feb 08
1001 P1 01 Feb 08 1001 P2 18 Aug 06 1001 P3 25 Feb 07 1001 P4 12 Dec 07

Once done copy..PasteSpecial, Values
Then Select block of data in columns J to L. Cut and paste to column A below existing data.
Then Select block of data in columns G to I. Cut and paste to column A below existing data.
Then Select block of data in columns D to F. Cut and paste to column A below existing data.



Gavin