Date Formula

[Bob Matthews]

I have a spreadsheet with lots of daily transactions and a balance after each transaction. There could be more than one transaction per day while some days may have none. I am trying to work out a formula which identifies the first and last transaction for each month so I end up with a monthly opening and closing balance for each month. A sample of the data is below:

Date Type Qty Balance
01-Feb-99 Sales -2 103
01-Feb-99 Sales -1 102
08-Feb-99 Sales -2 100
26-Feb-99 Sales -3 97
26-Feb-99 Sales -6 91
01-Mar-99 Sales -1 90
01-Mar-99 Sales -2 88
31-Mar-99 Sales -4 84
31-Mar-99 Sales -2 82

Hope someone can help.

Thanks
Bob

[Derek]

Bob
I am sure there is an easier way to achieve what you are after by a pivot table, but since they aren't my strong point, here's an attempt at a formula solution

Assuming the following
A:A = Your Dates
B:B = Type
C:C = Qty
D:D = Balance

Put this formula in E6
=IF(MONTH(A6)>MONTH(A5),A6,IF(MONTH(A6)
Provided your dates are continuous without a break, you can scroll this formula up/down and it will show only the first and last day of the month (ie it returns the date in A6 if it is the first or last day of the month).
You can use this formula again in column F to return D6 (instead of A6) to get the balance for that date.
Since this formula looks at the date above and below it will only work if your dates extend far enough back and forward for it to see a change in the month.
Hope this helps
Derek

[ This Message was edited by: Derek on 2002-02-25 00:48 ]

[ This Message was edited by: Derek on 2002-02-25 00:50 ]

[Derek]

=IF(MONTH(A6)>MONTH(A5),A6,IF(MONTH(A6)

For some reason the post is truncating my formula, If this doesn't print out properly either,please add Derek

[ This Message was edited by: Derek on 2002-02-25 00:54 ]

[Derek]

This is so frustrating (this is the end of the formula). If it doesn't accept it this time, I give up
Derek

[ This Message was edited by: Derek on 2002-02-25 00:59 ]

[Mudface]

If your monthly balances are always lowest at the end of the month, then you could do the following. Create a pivot table by selecting you data and choosing Data-Pivot Table Report. Click Next on the first two screens. On Step 3 of the Pivot Table Wizard, drag the 'Date' entry into the 'ROW' part of the table. Drag Balance into the middle of the Table and double click on it and select Max from the list. Do the same, but select Min this time. Click Finish. On the pivot table that now appears, right-click on the Date heading and choose Group, then group into months. Your final table should look like the one below (get rid of the sub totals if you don't want them). Hope this is what you were after.


Feb Max of Balance 103
Min of Balance 91
Mar Max of Balance 90
Min of Balance 82
Total Max of Balance 103
Total Min of Balance 82

[Aladin Akyurek]

On 2002-02-24 21:46, Bob Matthews wrote:
I have a spreadsheet with lots of daily transactions and a balance after each transaction. There could be more than one transaction per day while some days may have none. I am trying to work out a formula which identifies the first and last transaction for each month so I end up with a monthly opening and closing balance for each month. A sample of the data is below:

Date Type Qty Balance
01-Feb-99 Sales -2 103
01-Feb-99 Sales -1 102
08-Feb-99 Sales -2 100
26-Feb-99 Sales -3 97
26-Feb-99 Sales -6 91
01-Mar-99 Sales -1 90
01-Mar-99 Sales -2 88
31-Mar-99 Sales -4 84
31-Mar-99 Sales -2 82

Bob,

What follows might be a bit heavy (if not convoluted).

I'll assume that A1:D12 houses the sample data including labels. By the way, I added 2 more records to the sample:

{"Date","Type","Qty","Balance";
36192,"Sales",-2,103;
36192,"Sales",-1,102;
36199,"Sales",-2,100;
36217,"Sales",-3,97;
36217,"Sales",-6,91;
36220,"Sales",-1,90;
36220,"Sales",-2,88;
36250,"Sales",-4,84;
36250,"Sales",-2,82;
36253,"Sales",-7,78;
36278,"Sales",-8,67}

In F1 enter:

=MATCH(9.99999999999999E+307,A:A)

In F2:H2 enter:

{1999,
"First Transaction",
"Last Transaction"}

In F3:F14 enter:

{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"}

Note 1. I've chosen 3-letter month names. A different choice requires adapting the formulas that follow.

In G3 array-enter:

=INDEX(D:D,MATCH(MIN(IF(TEXT(OFFSET($A$2,0,0,$F$1,1),"mmm")=$F3,(OFFSET($A$2,0,0,$F$1,1)))),OFFSET($A$2,0,0,$F$1,1),0)+1)

In H3 array-enter:

=INDEX(D:D,MATCH(MAX(IF(TEXT(OFFSET($A$2,0,0,$F$1,1),"mmm")=$F3,(OFFSET($A$2,0,0,$F$1,1)))),OFFSET($A$2,0,0,$F$1,1),0)+COUNTIF(A:A,MAX(IF(TEXT(OFFSET($A$2,0,0,$F$1,1),"mmm")=$F3,(OFFSET($A$2,0,0,$F$1,1))))))

Note 2. In order to array-enter a formula, you need to hit control+shift+enter at the same time, not just enter.

Select G3:H3 and copy down till the Dec row.

This is what you get to see in F1:H6, showing only Jan to Apr results:

{12,"","";
1999,"First Transaction","Last Transaction";
"Jan",#N/A,#N/A;
"Feb",103,91;
"Mar",90,82;
"Apr",78,67}

Note that we have #N/A for Jan, due to the absence of data regarding that month. If you'd like not to see #N/A's, you can apply Cond Format to the result range to whiten them.

Aladin