# Thread: coded in to monthly (Excel2003)

1. ## 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

2. ## 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. ## 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. ## 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

5. ## 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.

6. ## 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. ## 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. ## 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. ## 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
•