Subtracting Days & Months from Specific Dates Using a Separate Day/Date Column.

Andrew Watts
Andrew Watts ✭✭✭✭
edited 02/22/23 in Formulas and Functions

Hello all,

I am having some trouble trying to use the correct formulas to subtract both Days and Months from certain dates. I am also incorporating a helper column that would allow me to change the days or months if needed.

Hope this makes sense.

I have also attached a screen shot of the table I am working with at the moment.

FYI only one column will subtract months or days. Never both. Hopefully the attached image will clear that up.


Hope someone can help me! Thanks in advance.


Capture04.JPG


Tags:
· Share on FacebookShare on Twitter

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @Andrew Watts

    That's my mistake. This one I've tested and I believe it works with everything.

    You've got your [Date Column] column value and you want to find the date value that is this [Number of months before] column value before that date:

    =IF(MONTH([Date Column]@row) - [Number of months before]@row <= 0, DATE(YEAR([Date Column]@row) - 1, 12 + (MONTH([Date Column]@row) - [Number of months before]@row), DAY([Date Column]@row)), DATE(YEAR([Date Column]@row), MONTH([Date Column]@row) - [Number of months before]@row, DAY([Date Column]@row)))

    English: If the (date column month minus the number of months before) is less than or equal to 0, make a date value with last year's year, a month that equals 12 + the negative result of (date column month minus the number of months before), and the same day value; otherwise, make a date value with this year's year, a month equal to (date column month minus the number of months before), and the same day value.

    From my test sheet:

    image.png


    image.png


    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

    · Share on FacebookShare on Twitter

Answers

  • Andrew Watts
    Andrew Watts ✭✭✭✭

    @Jeff Reisman I have tried to use your formulas used in other community pages with no luck. Any chance you can help me here?

    Thanks

    Andrew

    · Share on FacebookShare on Twitter
  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    https://community.smartsheet.com/discussion/comment/363645#Comment_363645

    Hi Andrew -

    Could you confirm that the date columns are actually date-type columns with actual date values? And that the columns containing your formulas - where you want your calculated date values - are also date-type columns? The little blue calendar icon should appear on the right-hand side of the cell when you click on one of them.

    Next, what are the formulas you have been trying to use, and in which columns are you using them? I'm assuming from your screenshot that you're trying to populate "Specs & Prices Freeze" with the date 2 weeks prior to the date in "Marketing Kick-Off", yes? And "Photos Samples Handover" with the date 8 weeks after the "Marketing Kick-Off", and so on?

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

    · Share on FacebookShare on Twitter
  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    @Andrew Watts Ok assuming that all your date columns are date-type with date values, and your formula columns are date-type columns as well...

    Subtracting weeks is easy, because you can just subtract the number of days in those weeks. Want to subtract 8 weeks from a date value?

    =[Date Column]@row - 56

    Because 7 days x 8 weeks = 56 days.

    image.png

    As you can see, it calculates the year correctly, etc. Nothing special you have to do. (Mine is in USA date style, MM/DD/YY.) Feb 1 2023 minus 8 weeks is Dec 7 2022.

    You have the option to do the same thing with months, if you just want to generalize a month as 30 days. 3 months = 90 days, 8 months = 240 days, etc. But if you want the day of the month to stay the same, and you need to be able to cross years correctly, you need a slightly more complex formula that uses the functions DATE, YEAR, MONTH, DAY, and IF.

    We'll start with the one for subtracting 3 months from a date. We have to account for the months of Jan, Feb, and Mar - because subtracting three months from that brings us into the previous year, and you can't just straight up subtract 3 from the month number if it's 1, 2, or 3.

    =IF(MONTH(DateColumn@row) < 4, DATE(YEAR(DateColumn@row) - 1, 12 - MONTH(DateColumn@row), DAY(DateColumn@row)), DATE(YEAR(DateColumn@row), MONTH(DateColumn@row) - 3, DAY(DateColumn@row)))

    What we're doing here is using two options of formulas, one if the month number we're starting with is less than 4, and another formula if the month number is 4 or more. If it's less than 4, we start building a date value using the DATE function - the syntax is DATE(yyyy, mm, dd). So we subtract 1 from the year value we have, then subtract the month value we have from 12, and keep the same day value. So if we start with Feb 1 2023 (2/1/23), we get Oct 1 2022 (10/1/22). In your formula, you can substitute the MONTH(DateColumn@row) - 3 with MONTH(DateColumn@row) - [3 months before sales Kick Off]@row in order to be able to change the number of months to subtract by changing the cell value. Then this will work with all the columns that you want to subtract a specified number of months from.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

    · Share on FacebookShare on Twitter
  • Andrew Watts
    Andrew Watts ✭✭✭✭
    edited 02/23/23


    · Share on FacebookShare on Twitter
  • Andrew Watts
    Andrew Watts ✭✭✭✭

    Hey @Jeff Reisman,

    Thanks a bunch for the fast response and detailed explanation! You are the best!

    Almost all is working now.

    Only thing that doesn't seem to work are dates that are more than 4 months. I have changed the (>) around but still not working.

    What am I missing?

    Thanks

    · Share on FacebookShare on Twitter
  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @Andrew Watts

    That's my mistake. This one I've tested and I believe it works with everything.

    You've got your [Date Column] column value and you want to find the date value that is this [Number of months before] column value before that date:

    =IF(MONTH([Date Column]@row) - [Number of months before]@row <= 0, DATE(YEAR([Date Column]@row) - 1, 12 + (MONTH([Date Column]@row) - [Number of months before]@row), DAY([Date Column]@row)), DATE(YEAR([Date Column]@row), MONTH([Date Column]@row) - [Number of months before]@row, DAY([Date Column]@row)))

    English: If the (date column month minus the number of months before) is less than or equal to 0, make a date value with last year's year, a month that equals 12 + the negative result of (date column month minus the number of months before), and the same day value; otherwise, make a date value with this year's year, a month equal to (date column month minus the number of months before), and the same day value.

    From my test sheet:

    image.png


    image.png


    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

    · Share on FacebookShare on Twitter
  • Andrew Watts
    Andrew Watts ✭✭✭✭

    Hey @Jeff Reisman,

    It all works!

    Cant thank you enough! You are a wizz.

    Have a great weekend!

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!