convert date to accounting period
Help!
Is there a way to convert a date to an accounting period?
eg.
21/7/10 to display 1
3/9/10 to display 3
date can be in a different format if necessary.
any hints will be appreciated!
Hi and welcome to the board
Not being an accountant, it would be helpful to get some more info about the rationale behind the conversion you are asking for.
Perhaps a sample sheet would help
Quoting entire posts clutters the forum and makes threads hard to read !
If you are pleased with a member's answer then use the Star icon to rate it
Click here to see forum rules
If 12 periods, with the last day of the month =MONTH(A1)
If non-month end period ends or 13 accounting periods, use a lookup table
Set up a lookup table (it works the same way as a phone book)
- list the period end dates in column a - say Jan this year thru Jan next year - 13 entires
- list the period number in column b - presumably 1, 2, 3 .......12, 1
add some sample dates in cells d1 to d5
put this formula in cell e, then copy to e2.
=VLOOKUP(D1,A$1:B$24,2)
good luck
Based solely on your 2 samples:
=MONTH(EDATE(A1,6)) where A1 contains the date
this assumes any date in July is to return a 1 and any date in Sept is to return a 3
in other words - any day within a calendar month is to return the month # of the same day 6 months ahead
Jan gives 7
Feb gives 8
Mar gives 9
etc
Is that what you want?
Last edited by Cutter; 04-24-2011 at 09:50 AM.
You won't be able to change the display of the actual date to reflect the fiscal month, but you could use a formula to calculate it.
With
A1: a date....e.g. 15-July-2011
This regular formula returns the fiscal month of that date:
B1: =1+MOD(MONTH(A1)-7,12)
In that example, the formula returns: 1
For other A1 values:
A1 value B1 display 21-Jul-2011 1 24-Aug-2011 2 27-Sep-2011 3 31-Oct-2011 4 04-Dec-2011 6 07-Jan-2012 7 10-Feb-2012 8 15-Mar-2012 9 18-Apr-2012 10 22-May-2012 11 25-Jun-2012 12 29-Jul-2012 1
Does that help?
The OP being one month old, I'm afraid there will be no reaction
Quoting entire posts clutters the forum and makes threads hard to read !
If you are pleased with a member's answer then use the Star icon to rate it
Click here to see forum rules
Thanks for the alert. I saw the latest reply and didn't even notice the staledated OP. I guess I can stop holding my breath waiting for a reply, eh?
Last edited by Ron Coderre; 04-24-2011 at 06:56 PM.
I didn't notice it either.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks