Calculate latest quarter end

[dgabor]

Hi All,

In an older thread I have seen the below solution for calculating the latest quarter end from a given date and it seems to work. However, I am not sure how the formula works.

Could you please explain?

=LOOKUP(B1,DATE(YEAR(B1),{1,4,7,10},0))

Thank you!

 

[Eric W]

Welcome to the forum!

The array constant {1,4,7,10} has the month numbers of January, April, July, and October. When you enter multiple values in an array that usually has 1, Excel will calculate all the values. So DATE will calculate all 4 of these:

DATE(YEAR(B1),1,0)
DATE(YEAR(B1),4,0)
DATE(YEAR(B1),7,0)
DATE(YEAR(B1),10,0)

The day value in DATE is 0 for all four cases, so it calculates the day before if you used 1, or the last day of the previous month. So you'll end up with an array holding the 4 end days of the quarters:

12,31,year-1
3,31,year
6,30,year
9,30,year

Then finally LOOKUP takes the date in B1, and searches for the highest value in that list that is less than B1, which gives you the date you want.