Subtract 6 Months from Date in Another Cell

kcain

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.

Tim Meeks

kcain, 

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

 

let me know if that works.

 

tim

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

kcain

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?

 

Jason Rowe

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

 

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