Member Ben asks how to add 1 month to a date.
Explore ↓
Hey there stranger!
Sign up to get access.
Ben Asks: How Do I Add 1 Month?
About this Tutorial
Sheet Resources
Featured Formulas
Video Transcript
Hello, welcome. So Ben is asking how do I add one month? Really simple question, right? But what, yeah, I'll show you what happens when we try to add one month. So we go equals today, and today is July 7th. If I want to add 30 days, it's August 6th, 31 days. It's August 7th. Okay. So that's fine, right?
If I know the number of days, but I want to add one. . Right. And it's really hard because, you know, date, you need to like figure out how many days are within, like technically speaking most of the time, most of the time when you're trying to add one month, you're just trying to add one to the month. Like if today, if you want to go from the 27th to the 27th of 27th of January to the 27th of February, you're just trying to change that second.
So what ends up happening is there is actually a Google formula for this called E date, and literally all it does is you say the number of months and it then changes the start date to to the next month. That that date is. There are some pitfalls that I wanna share with you. You can actually do, this is really funny, you can do a negative number here.
So you can say, you know, maybe the dates. Like the 27th of December until the 27th of fe January, or positive three months. And literally what it's going to do is just change the month to the next month number. This, I think, is like, again, most of the time like. Like 360 days out of the year. It's absolutely fine.
That is like what you're trying to do. But there's a couple of, of little tiny pitfalls that are gonna happen. And, and also like if you want to like do a range, like what you need to do is we need to just Use this like start date, end date. You use e date to add a month and then you can get the range in a text format.
But you have to change the numbers to text by using text. So we con we can't just concatenate. I'll show you what happens when we try to concatenate these. So we conka concatenate that is hard to spell. We want to concatenate the start date. We want to add a little hyphen in between. and we want the end date.
This is what happens. We get name, why name? Because, oh, because . I did spell concatenate wrong. Con concatenate. That's okay. This is the, it's not a name or you get it's numbers. You, it is taking the number of that date. 44,000. We are, we're in the 44000th date. and it's, it's changing those into these numbers, right?
And, and this is the number you get if you change a date. Now, let's go here. Let's format number, clean text. Nope. Format. We're trying to, let's do this today. And then we want it, here we go. We had to formatted as a number. We formatted as a number. And we have 44,000 vote and 19, but we don't want that.
We want a date. As a date, right? So so what we need to do is we need to add this text. And what we do is we add this text, we take the number this date we, let's do it here, text, take this date, and we format it in the format we want. And in this case, we want dd, which is two date numbers, and then a month.
Month Mm. For the two month numbers. You can also do. You can also do d one D, one M two Ys, or in this case, we want everything. We wanted to make. Force it to be two. Date, date, d d for date. Date mm. For two M numbers. And then four y. And even if you do three for some, because it's always gonna be four, you can get away with doing three if you wanna save one stroke, keystroke.
And then there's your format. Your formatted text. And in our range we have concatenated, our formatted text with a hyphen and then have our other formatted text here. So we've taken this sort of number, hyphen number and changed it into date, hyphen date, and we've added one month. So here is the pitfall though.
Stop right there. Here is the pitfall. Here are the few dates that are gonna have some weirdness going on. Okay, so we have eDay here. All we're doing is taking our date, adding one month. But at the end of January, if you go the 28th of January, it's the 28th of February, and if you go to the 29th of January in for this particular year, there is leap year.
And then what happens when you go 30th? It doesn't change. So if. , it depends on what a month is here, right? If, if you are, this may be correct. If you are trying to say the very last day the second to last day of January to the last day of February this year, this is correct. But maybe you're trying, like when you say one month, you're adding one month, figure out what does that actually mean?
Does that actually mean adding 30. Does that mean adding 31 days? What happens when those dates at the end of the month? One is the 31st and then there is no 31st on the next date. So, and the next month, right? So even January 31st still says 29. What is the difference between these numbers is not 30.
This is, this is, might not be a month difference to you. Or you might just say, you know a bill is due on the very last day of the. And that's fine. Or you might not even run into this. If you are working with dates at the beginning of the month, you'll never, a hundred percent of the time, you will never run into this.
If you are only working with days from one to 28, a hundred percent of every month has 28 days. So very interesting little pitfall here. Let me show you, actually, let's try, actually, I just wanna know for myself, if we go to January, 2021, and we hit the 29th. All right. There is no 29th of February that year.
So for three days, the 29th, the 30th, and the 31st, it's all gonna be February 28th is one month later. That might not be true for your particular case. Again, 360 days a year, it's fine. actually less like this date and then every 31st except for the July to August, right? So maybe 358 days a year.
It's fine. So very interesting way. E date, who would've ever thought you would never like intuitively think ate is the way to add a month, but it literally returned to. A specified number of months before or after another date, again, you're gonna have the issue where if it's the end of the month, you're gonna have some weirdness there.
So I would double check what is, what is it that you define as a month? And perhaps there is a different method you can use. Like you can literally use just plus 30. If you want to just add 30 date days, you can do this equals plus 30. And there you go. That's now February 3rd, no, March 2nd. March 2nd. Oh my god.
Sorry. I'm I I'm used to month and then date. So this is January 31st at 30 days, and you have March 2nd. So maybe this is the correct span for you. Or you literally just want to have the end of the month be. number you want. Hopefully this is really helpful and hopefully you learn about e dates.
Bye.
If I know the number of days, but I want to add one. . Right. And it's really hard because, you know, date, you need to like figure out how many days are within, like technically speaking most of the time, most of the time when you're trying to add one month, you're just trying to add one to the month. Like if today, if you want to go from the 27th to the 27th of 27th of January to the 27th of February, you're just trying to change that second.
So what ends up happening is there is actually a Google formula for this called E date, and literally all it does is you say the number of months and it then changes the start date to to the next month. That that date is. There are some pitfalls that I wanna share with you. You can actually do, this is really funny, you can do a negative number here.
So you can say, you know, maybe the dates. Like the 27th of December until the 27th of fe January, or positive three months. And literally what it's going to do is just change the month to the next month number. This, I think, is like, again, most of the time like. Like 360 days out of the year. It's absolutely fine.
That is like what you're trying to do. But there's a couple of, of little tiny pitfalls that are gonna happen. And, and also like if you want to like do a range, like what you need to do is we need to just Use this like start date, end date. You use e date to add a month and then you can get the range in a text format.
But you have to change the numbers to text by using text. So we con we can't just concatenate. I'll show you what happens when we try to concatenate these. So we conka concatenate that is hard to spell. We want to concatenate the start date. We want to add a little hyphen in between. and we want the end date.
This is what happens. We get name, why name? Because, oh, because . I did spell concatenate wrong. Con concatenate. That's okay. This is the, it's not a name or you get it's numbers. You, it is taking the number of that date. 44,000. We are, we're in the 44000th date. and it's, it's changing those into these numbers, right?
And, and this is the number you get if you change a date. Now, let's go here. Let's format number, clean text. Nope. Format. We're trying to, let's do this today. And then we want it, here we go. We had to formatted as a number. We formatted as a number. And we have 44,000 vote and 19, but we don't want that.
We want a date. As a date, right? So so what we need to do is we need to add this text. And what we do is we add this text, we take the number this date we, let's do it here, text, take this date, and we format it in the format we want. And in this case, we want dd, which is two date numbers, and then a month.
Month Mm. For the two month numbers. You can also do. You can also do d one D, one M two Ys, or in this case, we want everything. We wanted to make. Force it to be two. Date, date, d d for date. Date mm. For two M numbers. And then four y. And even if you do three for some, because it's always gonna be four, you can get away with doing three if you wanna save one stroke, keystroke.
And then there's your format. Your formatted text. And in our range we have concatenated, our formatted text with a hyphen and then have our other formatted text here. So we've taken this sort of number, hyphen number and changed it into date, hyphen date, and we've added one month. So here is the pitfall though.
Stop right there. Here is the pitfall. Here are the few dates that are gonna have some weirdness going on. Okay, so we have eDay here. All we're doing is taking our date, adding one month. But at the end of January, if you go the 28th of January, it's the 28th of February, and if you go to the 29th of January in for this particular year, there is leap year.
And then what happens when you go 30th? It doesn't change. So if. , it depends on what a month is here, right? If, if you are, this may be correct. If you are trying to say the very last day the second to last day of January to the last day of February this year, this is correct. But maybe you're trying, like when you say one month, you're adding one month, figure out what does that actually mean?
Does that actually mean adding 30. Does that mean adding 31 days? What happens when those dates at the end of the month? One is the 31st and then there is no 31st on the next date. So, and the next month, right? So even January 31st still says 29. What is the difference between these numbers is not 30.
This is, this is, might not be a month difference to you. Or you might just say, you know a bill is due on the very last day of the. And that's fine. Or you might not even run into this. If you are working with dates at the beginning of the month, you'll never, a hundred percent of the time, you will never run into this.
If you are only working with days from one to 28, a hundred percent of every month has 28 days. So very interesting little pitfall here. Let me show you, actually, let's try, actually, I just wanna know for myself, if we go to January, 2021, and we hit the 29th. All right. There is no 29th of February that year.
So for three days, the 29th, the 30th, and the 31st, it's all gonna be February 28th is one month later. That might not be true for your particular case. Again, 360 days a year, it's fine. actually less like this date and then every 31st except for the July to August, right? So maybe 358 days a year.
It's fine. So very interesting way. E date, who would've ever thought you would never like intuitively think ate is the way to add a month, but it literally returned to. A specified number of months before or after another date, again, you're gonna have the issue where if it's the end of the month, you're gonna have some weirdness there.
So I would double check what is, what is it that you define as a month? And perhaps there is a different method you can use. Like you can literally use just plus 30. If you want to just add 30 date days, you can do this equals plus 30. And there you go. That's now February 3rd, no, March 2nd. March 2nd. Oh my god.
Sorry. I'm I I'm used to month and then date. So this is January 31st at 30 days, and you have March 2nd. So maybe this is the correct span for you. Or you literally just want to have the end of the month be. number you want. Hopefully this is really helpful and hopefully you learn about e dates.
Bye.