Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Jul 2005
    Location
    Jakarta, Indonesia
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    coded in to monthly (Excel2003)

    I have daily transaction in one year that need to be coded per month, so I can analyze per month. I can't do with pivot, because my monthly period is bet ween date of 21 to 20 of following month. I try with if function but it has limitation up to 8 condition.

    Any help would be appreciated

    thanks Indra
    Attached Files Attached Files

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: coded in to monthly (Excel2003)

    If the "month of record" is consistently the prior month up to the 21st, and the current month thereafter you can determine the "month of record" by formula, and use that in the PT.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: coded in to monthly (Excel2003)

    Why is 27-Jan-07 included with February and 6-Mar-07 too? To be consistent with the rest, 27-Jan-07 should belong to January.
    You could use this formula in B10:

    =A10-20

    and fill down. Use column B in the pivot table and group by month.

  4. #4
    Star Lounger
    Join Date
    Jul 2005
    Location
    Jakarta, Indonesia
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: coded in to monthly (Excel2003)

    I post real case, that have monthly closing period between Jan 21 to Feb 20 and so on for following period. I need to group transaction date in one period in separate column so I could create report per month of period transaction.

    I try with if formulae, but limited only for 8 month of period grouping, which I need 12 per year or more if the data pass through different year

    regards

    Indra
    Attached Files Attached Files

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: coded in to monthly (Excel2003)

    Enter this formula in C28:

    =DATE(YEAR(B28),MONTH(B28-20)+1,1)

    Format this cell as Mar-01 (mmm-yy) or if you want to see only the month, as the custom format mmm.
    Fill down as far as needed.

    See atached version. I've added the formulas and a simple pivot table.
    Attached Files Attached Files

  6. #6
    Star Lounger
    Join Date
    Jul 2005
    Location
    Jakarta, Indonesia
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: coded in to monthly (Excel2003)

    Hans, It will takes sometime for me to understand the formulae. But the impotant things it really works!

    Many many thanks

    Indra

    .... is there a way to change my ID becoming HansVII ?

  7. #7
    Star Lounger
    Join Date
    Jul 2005
    Location
    Jakarta, Indonesia
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: coded in to monthly (Excel2003)

    One more thing Hans, if date is 1-Jan-08 result of formulae is Jan-09 instead of Jan-08..
    any thoughts?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: coded in to monthly (Excel2003)

    Sorry, the formula in C28 should have been

    =DATE(YEAR(B28-20),MONTH(B28-20)+1,1)

    and similar in the cells below.

  9. #9
    Star Lounger
    Join Date
    Jul 2005
    Location
    Jakarta, Indonesia
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: coded in to monthly (Excel2003)

    thanks again Hans,

    cheers

Posting Permissions

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