Date Formula

Bob Matthews

New Member
Joined
Feb 23, 2002
Messages
3
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
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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)<MONTH(A7),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
 
Upvote 0
=IF(MONTH(A6)>MONTH(A5),A6,IF(MONTH(A6)<MONTH(A7),A6,""))

For some reason the post is truncating my formula, If this doesn't print out properly either,please add<MONTH(A7),A6,"")) to the end of my previous post
Derek
This message was edited by Derek on 2002-02-25 00:54
 
Upvote 0
<MONTH(A7),A6,""))

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
 
Upvote 0
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
 
Upvote 0
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
<snip>

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
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top