Excel LARGE and EOMONTH Array Function

I’m working on a dashboard for a client and I’ve set it up so that they can choose which month they want to display in their report.

When they choose a new month the header in the report automatically updates to show the new 'From' and 'To' dates.

New data is added daily, so at times the period of the report may only be a partial month.

So, how do I find the ‘To’ date for my dashboard header?

Essentially I want to find the largest date for the chosen month.

Let’s say the dates are in a column with a named range db_date, and we currently have data for 1^st January through to 24^th November 2011.

We know that the LARGE function can find the largest date in the whole range.

So

=LARGE(db_date,1)

would correctly find the date of 24^th November.

But what if they chose October? They’d still end up with a ‘To’ date of 24^th November when it should read 31^st October.

What we need is a LARGE IF function that said; find the largest date that is >= the ‘From’ date and <= to the last day of the ‘From’ month.

Unfortunately there’s not one function, but we can create a formula with an array like this:

{=LARGE(IF(db_date>=D3,IF(db_date<=EOMONTH(D3,0),db_date,0),0),1)}

In D3 we have the first day of the chosen month.

Remember with array formulas you enter them by pressing CTRL+SHIFT+ENTER and shazam, Excel magically puts the curly brackets in for you.

In English our formula reads:

=LARGE(IF(the date in the db_date column is >=D3, and IF(the date in the db_date column is <= the last day of the month in cell D3), find the LARGEST date in the db_date column that meet this criteria)

The EOMONTH function returns the serial number of the last day of the month before or after a specified number of months.

So

=EOMONTH(D3,0)

gives you 30/11/2011 (where D3 contains the date 1/11/2011)

=EOMONTH(D3,1)

gives you 31/12/2011

And

=EOMONTH(D3,-1)

gives you 31/10/2011.

Notes on EOMONTH:

1. It is not recommended that you enter your dates as text e.g.

   =EOMONTH(“1/11/2011”,1)

   Instead refer to a cell containing the date or use the DATE function:




=EOMONTH(DATE(2011,11,1),1)

2. Excel 2003 users will need to install the Analysis ToolPak add-in to use the EOMONTH function.

• On the Tools menu, click Add-Ins.
• In the Add-Ins available list, select the Analysis ToolPak box, and then click OK.

Bonus Use for EOMONTH Function:

=EOMONTH(NOW(),0)

Gives you the last day of the current month.

Use it at your will.

For more on the LARGE Function.

For more on Array Formulas.

Do you have a different way of tackling this? Let me know in the comments below. I'd love to hear your ideas.

And don't forget to sign up for the weekly Excel newsletter below and get great tips to your inbox each week.