Formula to subtract one month from a date

Options
✭✭✭✭✭
edited 09/02/20

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:

• Overachievers Alumni
Options

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)

• ✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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.

• Options

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?

• ✭✭✭✭✭✭
Options

@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.

• ✭✭✭
Options

@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?

• ✭✭✭✭✭✭
Options

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

• ✭✭✭
Options

Simple solution. Thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!