From the Not Just Numbers blog:
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.
=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”.