We want to know, based on today's date, what is the start of the month and what's the date at the end of the month.
Academy ↓
Hey there stranger!
Find the Start date of the Current and End date of the Current Month
About this Tutorial
Sheet Resources
Video Transcript
Hi. So I thought of this video because this came up twice this week. As I was working on sheets for other people and I realized I needed to solve this problem and I kept Googling for it. So I thought, oh my God, I need to talk about this. How do you figure out the start and end of a month? Now, this is used a lot and I am not sure why.
One formula but not the other. We'll go into that in this video. We have a formula today equals today. You might know that, you might know that, but you might not know how to get to start of the month and end of the month based on a date. So we'll get into that in a video. In this video, I want to first tell you a couple use cases for this, that just for some reason, twice this week, I've had, I've run into.
All right, so you know today, and we need to use today, start of the month and end of the month. The issue is I've used this in at least two sheets this week and before. I've used this a lot where I need to keep track of, say spend within a month based on transactions. Like I want to filter transactions within a month or in, in the case I want to create a calendar and I want to.
What today is, and I wanna show a piece of that calendar. So I created a, like a year long calendar. But I really wanted to show just what is this month? Because humans, as humans, we think of these segments of time as days, weeks, months, quarters, and years, right? Month is one of the most common in there, and we want to know.
The start and end of the month. So we can display a calendar that shows the start and end of the month, what that shows transactions, filters some kind of data. In one of the cases it was filtering revenue coming in, needed to attract revenue coming in within the start and end of that month. So we needed those dates.
Alright, so let's see how we figure this out. Now, today is is super easy. Equals T O D A Today, we two, the parenthesis, and we have today, today's July 4th. So when was the first day? Oh, and the biggest challenge we're gonna run into with this is that you can't do simple math because months there is. Not all the months have 30 days.
Not all the months have 31 days. Each month is different. You can sort of one of the simplest logical ways to do this is create a little table on a sheet, on a tab, add that and use V lookup. But we can go a little deeper in this and we can do this ad hoc. Okay, so let's do end of the month first, because that's the simplest one, because Google Sheets has.
Formula for it. It's e o m o n t h e end of month. And it says here, last day of a month before or after a date. So we can do this, we can do parenthesis today, and we can say, this month is, is a little hard to understand, but it's off. How much do you wanna offset from what you're doing? So this is actually a pretty powerful formula.
Most of the time we're gonna use zero. In this case, we're gonna use zero. We wanna know what's the end of the month. This month we're gonna use zero. And so we hit enter there. Let's show, I, I just wanna show you before we go on, what happens if we do one here? That is the end of next month. If we do negative one, it's the end of last month.
This is super cool because within this one formula, all you have to know is EO month. You can get two. You can do 10, 12, you can do a year from now. What's the end of the month? A year from now? A 12, 12 month offset. But it's so complicated to remember that. But let's do zero, because that's what we want to do for this particular video.
We wanna know what's the end of this month now, what's the end of the current month? Okay. The start of the month is, is harder because if you do equals start of month, there's no formula. If you do beginning of month, there's no, there's no formula. B O N T H. What we do is a little bit of math. We do EO month, right?
We're gonna get the end of the month of today, and we wanna offset negative one. So we wanna know what's the end of the month last month, and we end parenthesis. So we have the exact same thing as we have over here, but now we just did negative one one month off, but we need to add one day. So at the end of the parent, Add plus one and ta-da.
That is the start of the month. So to refresh, to get today equals today to get end of the month EO month. And we use today within that. And we use offset by zero months to get the start of the month. We do end of the month negative one offset, and then we add one day, just plus one at the end. We add one day and we get.
The start of this month. So this gives you a really simple way to first get what's today, and that's gonna change every single day. Thankfully, it's automatic programmatic, it's just gonna keep changing. And now the start of the month and end of the month are also going to change as the days roll on and it gets into now August, September, these will also change.
This is the current month. And the end of the current month, and that's how you do it. This is really cool. I hope you have some cool use cases for this. Again, I've found twice in two separate sheets I've needed to do this just this week. So really cool. I had to create a calendar for someone and then I had to do some filtering and figure out.
Oh no, what's the first of the month and the end of the month? And then filter between that . So hopefully you've had some really cool use cases and obviously you found this video, so you now know how to find the start and end of the month. And you know this EO month, which is a very strange formula, very little used.
All right, bye.
One formula but not the other. We'll go into that in this video. We have a formula today equals today. You might know that, you might know that, but you might not know how to get to start of the month and end of the month based on a date. So we'll get into that in a video. In this video, I want to first tell you a couple use cases for this, that just for some reason, twice this week, I've had, I've run into.
All right, so you know today, and we need to use today, start of the month and end of the month. The issue is I've used this in at least two sheets this week and before. I've used this a lot where I need to keep track of, say spend within a month based on transactions. Like I want to filter transactions within a month or in, in the case I want to create a calendar and I want to.
What today is, and I wanna show a piece of that calendar. So I created a, like a year long calendar. But I really wanted to show just what is this month? Because humans, as humans, we think of these segments of time as days, weeks, months, quarters, and years, right? Month is one of the most common in there, and we want to know.
The start and end of the month. So we can display a calendar that shows the start and end of the month, what that shows transactions, filters some kind of data. In one of the cases it was filtering revenue coming in, needed to attract revenue coming in within the start and end of that month. So we needed those dates.
Alright, so let's see how we figure this out. Now, today is is super easy. Equals T O D A Today, we two, the parenthesis, and we have today, today's July 4th. So when was the first day? Oh, and the biggest challenge we're gonna run into with this is that you can't do simple math because months there is. Not all the months have 30 days.
Not all the months have 31 days. Each month is different. You can sort of one of the simplest logical ways to do this is create a little table on a sheet, on a tab, add that and use V lookup. But we can go a little deeper in this and we can do this ad hoc. Okay, so let's do end of the month first, because that's the simplest one, because Google Sheets has.
Formula for it. It's e o m o n t h e end of month. And it says here, last day of a month before or after a date. So we can do this, we can do parenthesis today, and we can say, this month is, is a little hard to understand, but it's off. How much do you wanna offset from what you're doing? So this is actually a pretty powerful formula.
Most of the time we're gonna use zero. In this case, we're gonna use zero. We wanna know what's the end of the month. This month we're gonna use zero. And so we hit enter there. Let's show, I, I just wanna show you before we go on, what happens if we do one here? That is the end of next month. If we do negative one, it's the end of last month.
This is super cool because within this one formula, all you have to know is EO month. You can get two. You can do 10, 12, you can do a year from now. What's the end of the month? A year from now? A 12, 12 month offset. But it's so complicated to remember that. But let's do zero, because that's what we want to do for this particular video.
We wanna know what's the end of this month now, what's the end of the current month? Okay. The start of the month is, is harder because if you do equals start of month, there's no formula. If you do beginning of month, there's no, there's no formula. B O N T H. What we do is a little bit of math. We do EO month, right?
We're gonna get the end of the month of today, and we wanna offset negative one. So we wanna know what's the end of the month last month, and we end parenthesis. So we have the exact same thing as we have over here, but now we just did negative one one month off, but we need to add one day. So at the end of the parent, Add plus one and ta-da.
That is the start of the month. So to refresh, to get today equals today to get end of the month EO month. And we use today within that. And we use offset by zero months to get the start of the month. We do end of the month negative one offset, and then we add one day, just plus one at the end. We add one day and we get.
The start of this month. So this gives you a really simple way to first get what's today, and that's gonna change every single day. Thankfully, it's automatic programmatic, it's just gonna keep changing. And now the start of the month and end of the month are also going to change as the days roll on and it gets into now August, September, these will also change.
This is the current month. And the end of the current month, and that's how you do it. This is really cool. I hope you have some cool use cases for this. Again, I've found twice in two separate sheets I've needed to do this just this week. So really cool. I had to create a calendar for someone and then I had to do some filtering and figure out.
Oh no, what's the first of the month and the end of the month? And then filter between that . So hopefully you've had some really cool use cases and obviously you found this video, so you now know how to find the start and end of the month. And you know this EO month, which is a very strange formula, very little used.
All right, bye.
Topics