EXCEL TIP: Making Accounting periods useful in Excel

Accounting periodsOften we have data, particularly in accounting systems, stored by Year and Period Number.

This may be weekly takings, with year being the accounting year and the period number being the week number, or it might simply be account movements or payroll by accounting year and accounting period, 1 to 12.

This is a sensible way to store the data but it can make selecting a range quite tricky, so I thought I would share a little trick I use to simplify this problem.

The problem:

Where data has a date attached, for example a transaction date, or a month end date for the type of data we are discussing here, we can use the following IF statement to determine whether a transaction/movement is within a date range:

Where A2 holds the transaction date and G1 holds the Start Date of the range we want to look at and H1 holds the End Date.

=IF(AND(A2>=$G$1,A2<=$H$1),"Yes","No")

Answers the question whether A2 is within the range G1 to H1.

(If you need a refresher on the IF statement, take a look at my earlier post).

Where we have a Year and Period Number, this doesn't work. If the range straddles a year end, you want to include, say, period 12 from the first year but not from the second.

The Solution:

To make it much easier, we need to create a working column that combines the two into a sequential number. We can do this with a simple calculation:

Where B2 holds the year and C2 holds the period we can enter the following in D2:

=(B2*100)+C2

This will convert the year and period into a 6-digit number where all periods are in sequence, so...

Where B2=2012 and C2 =7, the formula returns 201200 + 7 = 201207

It is possible that the year and period are held as text rather than as a number, in this case you would use the VALUE function to convert the text to values:

=(VALUE(B2)*100)+VALUE(C2)

If we now state the start and end of the period range in the same format (in cells G1 and H1 as before), we can use the same approach as for dates to identify the range using our working column D, i.e.

=IF(AND(D2>=$G$1,D2<=$H$1),"Yes","No") 

Column D could also be used to sort the data, as we now how have a column that identifies the correct chronological order.

If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".

Comments

Arithmetic with text formatted numbers

Alf | | Permalink

You say that where the year and period are stored in text format the VALUE function will be needed but will the act of performing an arithmetical operation on, eg B2*100, not force its conversion to number format without the use of VALUE?

Add comment
Log in or register to post comments
This blog

Popular posts from Glen Feechan's Not Just Numbers blog - The blog for those who know it's not just about the numbers. Typical content included is primarily Excel tips and other comment relevant to those responsible for finance in their business. Glen develops spreadsheets for clients all over the world via needaspreadsheet.com and helps accountancy practices to make better use of Excel through his Excellent Accountancy business.

Get The 5 Excel features that you need to know free, by subscribing to Not Just Numbers here.