Formula to subtract one month from a date

M. David
M. David ✭✭✭✭✭
edited 09/02/20 in Formulas and Functions

Hello.

I need to subtract one month from a date. I have tried =Date-30, and then minus 60, etc. By month minus 5, I was quite a bit off from the first of the month. If the Date being evaluated is say 4/1/20, I'd like the first calculation to produce 3/1/20, and the next row to produce 2/1/20, and so on. I did find a formula that worked as long as it didn't cross years.

=DATE(YEAR(date12), MONTH(date12) - 1, DAY(date12))

If the date being evaluated was 4/1/20, by the fourth calculation, it messed up because if would cross from 2020 to 2019. I need to evaluate date minus 24 months, so being off a little in month one, makes for a huge difference in month 24.

All help is appreciated.

Thanks.

Tags:

Best Answer

Answers

  • MCorbin
    MCorbin Overachievers Alumni

    I do a lot of metrics sheets with rolling 12 month windows - and initially ran into the same problem.

    There are a couple of tricks:

    1) Use a "test date" in the middle of the month - mine uses the 15th day of the current month =DATE(YEAR(Today$1), MONTH(Today$1), 15) (Today$1 is just =today() I make that a separate cell as it allows me to easily change the starting date of my rolling time period) (see screenshot below). That way, your -30, -60, -90 days calculations always land somewhere in the middle of the month you're looking for, and then you can modify your =DATE() formula to set the actual day.

    2) Your date formulas have to factor both the year and the month. The image below is from a rolling 12-month metrics sheet. I use the first row to multiply *30 to calculate how many days from my test date to evaluate (You'll see that in my formula - it makes it so I can just copy and paste my formula from column to column).

    So - to get the Year, here's the formula (this is the 11 months ago formula): =YEAR($Today$3 - (30 * [11 Months Ago]$2))

    Month: =MONTH($Today$3 - (30 * [11 Months Ago]$2))

    I have a lookup that looks at the month and pulls back the last day of that month (since every month is different).

    And then I can do a formula that gives me everything between the start and end of that month:

    =SUMIFS({Calls Routed}, {Queue}, $Queue@row, {HD Date}, >=DATE([11 Months Ago]$3, [11 Months Ago]$4, 1), {HD Date}, <=DATE([11 Months Ago]$3, [11 Months Ago]$4, [11 Months Ago]$5))


    So for your question above, you may want to create a helper column that gives you the "Test Date" of

    =DATE(YEAR(date12), MONTH(date12), 15) We'll call that TestDt

    Then to get your date last month:

    =Date(year(TestDt-30),Month(TestDt-30),Day(Date12))


    (that was the long way around, but I thought the explanation might be helpful)

  • M. David
    M. David ✭✭✭✭✭

    Thank you both for your help with this. MCorbin, I understood most of you said there. Which is surprising as I know the software pretty well, but am still learning the intricacies of how formulas work together. I did end up going with Pauls solution as I figured it was less intensive and I'd try it first. Again, thank you both for the help. I do appreciate it.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️


    There are a few solutions out there for if you wanted to add or subtract a variable number of months (search for "EDATE"), and the solution I provided can be very easily adapted for different static numbers of months. All you have to do is remember that there are 12 months in a year.

    The solution has 2 different DATE functions.

    MONTH - 1 and (YEAR - 1 with MONTH + 11)

    1 + 11 = 12


    If you wanted to subtract 2 months:

    2 + 10 = 12

    MONTH - 2 and (YEAR - 1 with MONTH + 10)


    3 Months:

    3 + 9 = 12


    So on and so forth up through 11 months. The same principle can be used to add months as well. Once you go beyond 12 months it changes just a little in that you need to remember to incorporate an additional year for both, but the basic idea remains the same.

  • This is great! could you also help explain where in that formula i can edit the weeks prior? or to find weeks prior is that a different fomula?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Anna.. When dealing in weeks you are looking at a very different formula. There are quite a few examples floating around here in the Community regarding this, but if you are unable to locate something then please feel free to start a new thread. The more detail you can provide (including screenshots) the better, and you can "@mention" me if you would like so that I get an alert and an easy link to your thread so we can start working on it together there.

  • CWibben
    CWibben ✭✭✭

    @Paul Newcome I am trying to use this same formula to subtract one month from the date entered. It gives me an "Invalid..." response. Suggestions?


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @CWibben Make sure both columns are set as date type columns.

  • CWibben
    CWibben ✭✭✭

    Simple solution. Thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!