Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Conditional Date Calculation

Meenakshi Sundaram S
Meenakshi Sundaram S ✭
edited 12/09/19 in Archived 2016 Posts

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?

Tags:
· Share on FacebookShare on Twitter

Comments

  • Greg Gates
    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!

    · Share on FacebookShare on Twitter
  • Meenakshi Sundaram S
    Meenakshi Sundaram S ✭
    edited 08/22/16

    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.

    Capture.PNG

    · Share on FacebookShare on Twitter
  • Greg Gates
    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.

    Capture8.PNG

    · Share on FacebookShare on Twitter
  • Meenakshi Sundaram S
    Meenakshi Sundaram S ✭

    I just understood INDEX :)

     

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

    · Share on FacebookShare on Twitter
  • Meenakshi Sundaram S
    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. 

    · Share on FacebookShare on Twitter
  • Meenakshi Sundaram S
    Meenakshi Sundaram S ✭
    edited 08/25/16

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

     

    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?

    · Share on FacebookShare on Twitter
  • Greg Gates
    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.

    · Share on FacebookShare on Twitter
  • Meenakshi Sundaram S
    Meenakshi Sundaram S ✭

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

    · Share on FacebookShare on Twitter
This discussion has been closed.

Categories