Modify Formulas with different cenarios | General Excel Questions & Answers | Excel Forum

Hi Hava

You can give this a try. You will need to modify your headers to actual dates (all end-of-month dates) for this to work.

You may need to cover more columns to cater for future expansion.

I would have love to use an Excel Table for your range as it auto expands as you add columns.

Somehow Excel Tables converts the headers to Text so it will not work with the method I am showing you.

In the attachment you can enter any date and it will be converted to the end-of-month date for the summing.

The EOMONTH() function is used to calculate the dates which are 3, 6 or 12 months before the current date.

If you are unfamiliar with EOMONTH() you can take a look at this https://www.myonlinetraininghub.com/excel-eomonth-function

Hope this helps.

Sunny

Hi Hava

It does not matter what you entered in the Current Month date cell. If you entered 13/04/2016 it will be converted to either 30/04/2016 (E-O-M) or 01/04/2016 (B-O-M) depending on which Example you use.

Example 1 converts the current month date you entered to the End-Of-Month date so your data header must have E-O-M date.

Example 2 converts the current month date you entered to the Beginning-Of-Month date so your data header must have B-O-M date.

See if Example 2 is what you are looking for.

Sunny