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