Need to add 6 months and 3 months to a date

I have a date that other will populate, but I need to have two columns that calculated 3 months out from the date and 6 months out.


I have tried several formulas on the forum and nothing is working. I am completely stumped. If I was in excel this would have been done in a minute.



Tags:

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    @Deanna DeCoursey Ok. Here we go. I think we may have it here...


    Helper (text/number):

    =ABS(MONTH([Previous Due Date]@row) - MONTH(IF(Frequency@row = "Weekly", [Previous Due Date]@row + 7, IFERROR(DATE(YEAR([Previous Due Date]@row) + IF(CONTAINS("by", Frequency@row), 1, 0), MONTH([Previous Due Date]@row) + IF(Frequency@row = "Monthly", 1, IF(Frequency@row = "Quarterly", 3, IF(Frequency@row = "Semi-Annually (6 months)", 6, 0))), DAY([Previous Due Date]@row)), DATE(YEAR([Previous Due Date]@row) + 1, MONTH([Previous Due Date]@row) - IF(Frequency@row = "Monthly", 11, IF(Frequency@row = "Quarterly", 9, IF(Frequency@row = "Semi-Annually (6 months)", 6, 0))), DAY([Previous Due Date]@row))))))


    New Date (date):

    =IF(Frequency@row = "Weekly", [Previous Due Date]@row + 7, IF(OR(AND(Frequency@row = "Monthly", Helper@row <> 1, Helper@row <> 11), AND(Frequency@row = "Quarterly", Helper@row <> 9, Helper@row <> 3), AND(Frequency@row = "Semi-Annually (6 months)", Helper@row <> 6)), DATE(YEAR(IFERROR(DATE(YEAR([Previous Due Date]@row) + IF(CONTAINS("by", Frequency@row), 1, 0), MONTH([Previous Due Date]@row) + IF(Frequency@row = "Monthly", 1, IF(Frequency@row = "Quarterly", 3, IF(Frequency@row = "Semi-Annually (6 months)", 6, 0))), DAY([Previous Due Date]@row)), DATE(YEAR([Previous Due Date]@row) + 1, MONTH([Previous Due Date]@row) - IF(Frequency@row = "Monthly", 11, IF(Frequency@row = "Quarterly", 9, IF(Frequency@row = "Semi-Annually (6 months)", 6, 0))), DAY([Previous Due Date]@row)))), MONTH(IFERROR(DATE(YEAR([Previous Due Date]@row) + IF(CONTAINS("by", Frequency@row), 1, 0), MONTH([Previous Due Date]@row) + IF(Frequency@row = "Monthly", 1, IF(Frequency@row = "Quarterly", 3, IF(Frequency@row = "Semi-Annually (6 months)", 6, 0))), DAY([Previous Due Date]@row)), DATE(YEAR([Previous Due Date]@row) + 1, MONTH([Previous Due Date]@row) - IF(Frequency@row = "Monthly", 11, IF(Frequency@row = "Quarterly", 9, IF(Frequency@row = "Semi-Annually (6 months)", 6, 0))), DAY([Previous Due Date]@row)))), 1) - 1, IFERROR(DATE(YEAR([Previous Due Date]@row) + IF(CONTAINS("by", Frequency@row), 1, 0), MONTH([Previous Due Date]@row) + IF(Frequency@row = "Monthly", 1, IF(Frequency@row = "Quarterly", 3, IF(Frequency@row = "Semi-Annually (6 months)", 6, 0))), DAY([Previous Due Date]@row)), DATE(YEAR([Previous Due Date]@row) + 1, MONTH([Previous Due Date]@row) - IF(Frequency@row = "Monthly", 11, IF(Frequency@row = "Quarterly", 9, IF(Frequency@row = "Semi-Annually (6 months)", 6, 0))), DAY([Previous Due Date]@row)))))

«13

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!