Formula to subtract one month from a date

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.

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

Try this in row 2 with the first date being manually entered into row 1...

=IFERROR(DATE(YEAR(Date1), MONTH(Date1) - 1, DAY(Date1)), DATE(YEAR(Date1) - 1, 12, DAY(Date1)))

All comments

Paul Newcome

Try this in row 2 with the first date being manually entered into row 1...

=IFERROR(DATE(YEAR(Date1), MONTH(Date1) - 1, DAY(Date1)), DATE(YEAR(Date1) - 1, 12, DAY(Date1)))

MCorbin

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

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

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.

Anna..

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

@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

@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

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

CWibben

Simple solution. Thank you!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.