Conditional Date Calculation

Meenakshi Sundaram S

hi, I have months columns Aug-2016. .Sep-2016 etc., (just the names of the Column are months, but the Column property is Text/Number) and each month has a number value. I'd like to be able sum those numbers based on a condition - The current month's number + 4 more months from the current month should be summed. Eg: When we are in Aug-2016, the numbers from columns Aug, Sep, Oct, Nov and Dec should be summed. when we are in Sep-2016, the numbers from Sep...Jan-2017 should be summed. Is there any way to do this?

Greg Gates

Could you show a picture of what your sheet looks like? I'm having a little trouble envisioning your scenario. If every month is in its own column, and the sum is in the same column as the starting month, you should be able to do something like:

 

=SUM([Apr 2016]1:[Aug 2016]1)

 

Then, when you copy this formula across your sheet, the cells should automatically update to the new columns.

 

If that doesn't address your situation, definitely let me know!

Meenakshi Sundaram S

Sorry, I wasn't clear. The sheet looks something like this:

 

In the formula column, I'd like to enter a formula something similar to: =IF(MONTH(TODAY()) = 8, SUM([Aug-2016]1:[Nov-2016]1), IF(MONTH(TODAY()) = 9, SUM([Sep-2016]1:[Dec-2016]1), ............))

 

But if I have to do this for 24 columns, then it'll be a long formula. Is there a way to do it in a simpler way. Many thanks for your efforts and support.

Greg Gates

You can mostly accomplish this using the new INDEX function. I made a few assumptions here though:

1) Your spreadsheet starts with "Jan 2016" and doesn't skip any months

2) It would be ok to make a new sheet for each calendar year

 

If neither of these is ok, you can do some additional math to accomodate, but the general idea should be the same. 

 

INDEX returns the value of a specified column in a set of data. If your columns are organized by month, you can get a given month's value by passing in the numeric equivalent of the month. For example (note - my earlier months are hidden on this sheet):

 

 

Here's the actual formula text:

=INDEX([January 2016]1:[December 2016]1, 1, MONTH(TODAY())) +

INDEX([January 2016]1:[December 2016]1, 1, MONTH(TODAY()) + 1) +

INDEX([January 2016]1:[December 2016]1, 1, MONTH(TODAY()) + 2) +

INDEX([January 2016]1:[December 2016]1, 1, MONTH(TODAY()) + 3)

 

Now this value will automatically update depending on what the current month is! If you wanted to adapt it to handle moving into 2017, I would just change "MONTH(TODAY())" to "MONTH(TODAY()) + (12 * (YEAR(TODAY()) - 2016))"

 

I hope that helps with your issue! Let me know if it still needs some work.

Meenakshi Sundaram S

I just understood INDEX

 

But still not sure why the formula doesn't work for 24mths...

Meenakshi Sundaram S

This is brilliant! Thanks Greg. I'm not still sure how INDEX works though...I'm sure we are getting much closer to the solution.

=INDEX([Jan-2016]1:[Apr-2017]1, 1, MONTH(TODAY())) + INDEX([Jan-2016]1:[Apr-2017]]1, 1, MONTH(TODAY()) + 1) + INDEX([Jan-2016]1:[Apr-2017]1, 1, MONTH(TODAY()) + 2) + INDEX([Jan-2016]1:[Apr-2017]1, 1, MONTH(TODAY()) + 3)+INDEX([Jan-2016]1:[Apr-2017]1, 1, MONTH(TODAY()) + 4)+INDEX([Jan-2016]1:[Apr-2017]1, 1, MONTH(TODAY()) + 5)

 

I would like one formula for all 24months (2016 and 2017 together)..in this case I've created Columns till Apr-2017. However, the attached doesn't work. What am I missing here?

 

Thanks,

 

*******************************************************************

Update: Ignore me..all working now. Thanks a lot again. 

Meenakshi Sundaram S

Sorry, the next big question is that the columns in my sheet are not consecutive 

 

So, Jan-2016, Budget Jan-2016. Feb-2016, Budget Feb-2016....I'd like to sum only the Budget columns..Is this possible at all?

Greg Gates

Two things you would have to work around here. The easier one first - working around the unwanted columns inbetween.

 

Instead of adding MONTH(TODAY()) + 1 you could just add multiples of 2 instead. Now you'll be skipping over every other column. So that would look like MONTH(TODAY()) + 2, MONTH(TODAY()) + 4, etc... If that lines up with the wrong column, add an additional one to the result: (MONTH(TODAY()) + 1) + 2, (MONTH(TODAY()) + 1) + 4, etc.

 

The harder problem is going for 24 months. The MONTH function returns the month of the year, but as soon as the year starts over the result is going to go back to 1 for January. The workaround here involves accounting for the year as well. Your formula would have to change to look something like this (incorporating both solutions):

 

=INDEX([Jan 2016]1:[Apr 2017]1, 1, MONTH(TODAY()) + (YEAR(TODAY()) - 2016) * 12) + 

 INDEX([Jan 2016]1:[Apr 2017]1, 1, MONTH(TODAY()) + (YEAR(TODAY()) - 2016) * 12 + 2) +

 INDEX([Jan 2016]1:[Apr 2017]1, 1, MONTH(TODAY()) + (YEAR(TODAY()) - 2016) * 12 + 4)...

 

I hope that addresses all of your issues! Let me know if we still haven't quite hit the mark.

Meenakshi Sundaram S

Super. Thanks for all your help Greg. Really appreciate it.