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.

Subtract 6 Months from Date in Another Cell

kcain
kcain ✭
edited 12/09/19 in Archived 2017 Posts

Smartsheet Community,

 

I am trying to subtract 6 months from a date in another cell.  What is wrong with my formula?  The date from which I want to subtract 6 months is in row 203 in the "Date Completed" column.

 

=DATE([DATE COMPLETED]203), MONTH([DATE COMPLETED]203)-6

 

Can someone please help?  Thank you for your guidance.

Tags:
· Share on FacebookShare on Twitter

Comments

  • Tim Meeks
    Tim Meeks ✭✭✭✭✭✭

    kcain, 

    i think the formula is simply  =[Date completed]203-180 

     

    let me know if that works.

     

    tim

    · Share on FacebookShare on Twitter
  • Jason Rowe
    Jason Rowe ✭

    kcain,

    Try this formula. It should give you exactly 6 months.

     

    =IF((MONTH([DATE COMPLETED]203) - 6) < 1, DATE(YEAR([DATE COMPLETED]203) - 1, MONTH([DATE COMPLETED]203) - 6 + 12, DAY([DATE COMPLETED]203)), DATE(YEAR([DATE COMPLETED]203), MONTH([DATE COMPLETED]203) - 6, DAY([DATE COMPLETED]203)))

     

    Hope it works.

     

    Jason

    · Share on FacebookShare on Twitter
  • kcain
    kcain ✭
    edited 01/30/17

    Jason, it worked!  Thank you!  However, the formula messes up when the date equals a previous year.  Is there any way to account for that?

     

    · Share on FacebookShare on Twitter
  • Jason Rowe
    Jason Rowe ✭
    edited 01/31/17

    Dang!! What does it mess up on? It should have accounted for that in the if statement.

    For some reason in Smartsheet when you subtract months it doesn't think that taking January(1) and subtracting one should turn it into December(12), but rather that January(1) - 1 = 0 . So rather than looking like 3, 2, 1, 12, 11, 10(Mar, Feb, Jan, Dec, Nov, Oct), it subtracts as 3, 2, 1, 0, -1, -2(Mar, Feb, Jan, Error, Error, Error). So as soon as it tries to go to a previous year you get an error.

    But with the if statement it looks to see if that number goes less than 1 and if it does, then it subtracts 1 year and adds 6 months. Kind of a funky work around, but it should work.

     

    Sorry that was a ridiculously long explanation. Maybe you can paste the formula you used and we'll see if we can get it working.

     

    Good Luck

     

    · Share on FacebookShare on Twitter
  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Be careful.

     
    What is 6 months before Aug 31st?

    Dec 31st?

     

    Tim's equation (using 182 or 183 instead of 180) should be close enough.

     

    Depending on how you define it 6 months is likely 26 weeks (182)

     

    Craig

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

Categories