Monday, 30 July 2012

EXCEL TIP: Identifying irregular period-ends

A couple of weeks ago I did a post on identifying the calendar month-end from a particular date, and I promised to explain how to do this when your month-ends are irregular - as is still quite common in a manufacturing environment. Here is that post.

You may have any set of rules that govern when the month-end (or period-end if they're really irregular) is - "last Friday of the month" for example, but there are nearly always exceptions - usually around Christmas and other holidays.

The most flexible way to address this issue is to have a table of month-ends held in the spreadsheet that can be adjusted to whatever you want.

This can be a simple two column table with period start-dates in the first column and period end-dates in the second.

You can then use VLOOKUP to find the first start date that is before the transaction date in question. We can then return the period-end date from the second column.

For example, where the range containing the two-column table described above is called PERIODENDS and the transaction date in question is held in cell A1, we can use:

=VLOOKUP(A1,PERIODENDS,2) to return the corresponding period-end date.

If we wished to return the period number, we could add this in a third column in the table and use:

=VLOOKUP(A1,PERIODENDS,3)

Notice that I have not entered the optional fourth argument in the VLOOKUP function [range lookup]. This argument is normally entered as FALSE if you wish to look for an exact match, but if omitted (or entered as TRUE) the VLOOKUP identifies the first entry in the column (the column must be in ascending order) which is exceeded by the lookup value. In this case, the first period start date that the transaction date exceeds - i.e. exactly what we want!

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 comment:

1. Some sort of multi-brand approach seriously isn't concentrating on “many brands”, even so the brazilian hair lies in “high quality”. Dependant on truth, establishments will probably come to a decision what exactly models in order to develop in addition to the quantity of models will likely be formulated in line with authentic desires, subdivided promotes in addition to company toughness. Toyota’s all 5 models Toyota, SCION, Copen, Hino in addition to Lexus merged advertise 7. 47 mil motor vehicles with 2004, minute area on this planet. This multi-brand managing concentration lies in this tight managing with margins. Models independently can certainly write about many facts with facets like creation, paying for, siphon discussions, loan and many others, making sure that degree consequence can often engender some sort of reasonably competitive gain. Even so, model differentiations need to be highlighted in different facets of which people can certainly interact with. Differentiations usually are largely stated because of the using facets: selling price details, concentrate on people, model orientations, solution types, models, gross sales programmes, products and services, in addition to practical knowledge for example... Lexus is usually a team from the Toyota group structure, although it doesn't advertise merchandise and gives products and services as a result of brazilian hair sale programmes. Its’ 4S retail outlet is usually invested in produce great in addition to exclusive services ordeals; LVMH Collection will probably bargain while using the job site managing for everyone the models outlets hearings except for Kenzo, Louis Vuitton in addition to Fendi.