coded in to monthly (Excel2003)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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 ?

Reply | Quote

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

Reply | Quote

Sorry, the formula in C28 should have been

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

and similar in the cells below.

Reply | Quote

thanks again Hans,

cheers

Reply | Quote