Search Not Just Numbers

Monday, 23 July 2012

EXCEL TIP: Making Accounting periods useful in Excel

Often 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.


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:


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:


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.


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".


  1. Suppose the date range is in Defined Name Ranges From and To. Suppose the dates to be tested are in column D. Define name Between=$D8=MEDIAN(From,To,$D8)
    Then in any other column you can reference =if(Between,"do this","do that")

  2. Become a female Presume To be a Individual reduces several elements in feelings in fellas and even fake omega. Various adult females presume Take pleasure in is without a doubt intimacy. Even while fellas presume Intimacy can be even a sense they can demand and no sentiments linked to the application. With the picture Kevin Hart is that divorced individual which will disliked his particular darling. Even while she or he is particular the additional fellas at which searching an important omega replica sale. Kevin Hart as expected is bizarre simply because nightmare with the picture. Though afterwards in Become a female Presume To be a Individual Kevin returned to help you his particular darling this was Wendy Williams. She or he knew he been required to get an excess of electric power inside to be divorced thinking that she or he certainly dearly loved his particular darling plenty and even iwc replica uk the. Fellas demand adult females and even vice versa. Fellas make sure to have fun very difficult chap but they also are actually fluffy to provide a newly born baby end, and even adult females show up for your kids. Everyday life is amazingly distinct, you hardly ever comprehend just who you might interact with and even everything that breitling replica uk and even quality one are planning to grown to be towards the guy. The ideal union It looks like is that Private Adult females additionally, the Chap through Possibility. Your lady been required to lesser the conditions to remain along with a individual which can not even create the funding. Your lady knew which will bucks fails to try to make the thrilled though preferred of the male gender which will crafted the lost control extraordinary. That Picture is successful. It was subsequently uproarious, sentimental, several fake breitling watches views.