Date Formula
Date Formula
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Date Formula

  1. #1
    New Member
    Join Date
    Feb 2002
    Location
    Australia
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =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 ]

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    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 ]

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,785
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

      
    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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com