This article provides a practical, easy-to-follow guide on how to “Find the Start and End Dates” of a week given any date within the week using Make.com. Readers will gain valuable insights on:
- Understanding the concept of finding the first and the last day of any given week.
- Learning how to create a formula to calculate the start and end day of a week.
- Applying the formula to different scenarios for a more dynamic understanding.
- Deciphering how the formula works to enhance understanding and usage.
Find the Start and End Dates
I’m going to show you how to find the start and end dates for a week, given any date of that week. The idea here is you have a date and you need to figure out what day was Sunday for that week and what day was Saturday. So what’s the first day of the week and the last day of the week for any given date. I’ve got a two module scenario just to illustrate this and I’ve got my input date.
So it’s currently November 19th, 2023. And so I’m going to enter in the 22nd, which is a Wednesday. This scenario is going to find. Sunday of this week and Saturday. So the first and last days of the week.
All right. So in this scenario, I’ve got a few things in here.
I’ll show you what this is, but really here’s the Sunday date and here’s the Saturday date. We’re going to look at those formulas a little more closely. But let’s run this. And you can see my input date is the 22nd. So I’ve set some variables here. There’s one 22nd, so the input day of the week is Wednesday.
So that is the day of the 22nd. So if we go check our calendar, we see the 22nd is Wednesday. I’ve used a formula to figure that out. So we need to figure out how many days we need to get from Wednesday to Sunday, so that number is three. So we’re going to say subtract three days from today because today is Wednesday. And that gives us November 19th. And then we have to say how many days do we need to add, to get to Saturday’s date?
So that’s also three because we’re in the middle of the week on Wednesday. So to get to Saturday, we need to go 1, 2, 3 into the future and to get to Sunday, we need to go 1, 2, 3 into the past. So that is how we do that. And I’m using a formula here that tells us what day of the week it is and helps with our math.
Using Formulas to Find Sunday and Saturday Dates
So let me show you, uh, let me test this again for you. So let me do the 20th, which would be a Monday.
And I’m going to run that. I just want to show you that this, this is working correctly. So here’s our date. That’s a Monday. So how many days do we need to subtract to get to Sunday? Well, we just need? Just need to subtract one. All right to get from Monday to the previous day is one. And then to get to a Saturday’s date, we need to add five. So if we’re on Monday 1, 2, 3, 4, 5 gets us to Saturday.
So that’s how this works. So let me show you this formula. I’m going to go down here to Sunday’s date right here and this formula. Looks a little scary, we’ve got some nested formulas in here. So, what I’m doing is I’m taking I’m formatting the date. So I’m taking that date from the previous module. I’m mapping it here and I’m just formatting it in the format that I want. Now, what I have to do is I have to subtract the number of days to get from the current date to Sunday. So I’m doing an add days formula here. And so in the add days formula, If you put your date in which is here and then you put the number you want to subtract.
And this case I’m using a formula to, subtract that number. So I’m putting a negative and I’m using. Current date and I’m using this d variable. Now, if we go over here to the tokens for date and time, d, what that does is it gives you the day of the week from zero to six. Now. There is another one here. There’s a capital E that gives you the day of the week. One to seven. We don’t want that because that would throw our math off.
So what this is going to do. Is, it’s going to say. Uh, format date. So take the current dates. And format that as a zero to six. So for Sunday’s date, if we go back here. What this formula is saying Monday is the day of the week. So, if we want to go from Monday to Sunday, we’re going to subtract one.
So for Saturday’s date, we’re doing the same thing. We’re doing an add days, except in this case we’re doing or saying six minus whatever the day of the week number is. We’re saying Monday is the second day of the week. And an, our formatting Sunday is actually zero and Monday is one. So that’s why this works. So Monday equals one. So we’re saying six minus one equals five. And that’s how you get moving the date forward five days to give you Saturday’s date now. I feel like this is getting fairly complicated.
How to Use These Functions
Each of the functions below are actually three functions (Date and Time Functions):
- 1 addDays() function (underlined with red)
- 2 formatDate() functions (separately underlined in blue and green)
Get Sunday’s Date from any Date of the Week
Copy the following into your scenario to get started:
{{addDays(formatDate(3.`Current Date`; "MM/DD/YYYY"); "-" + formatDate(3.`Current Date`; "d"))}}
What This Scenario Should Look Like in Your Make.com Scenario:
addDaysformatDateYour Date Value;MM/DD/YYYY);–formatDateYour Date Value;d))
Get Saturday’s Date from any Date of the Week
Copy the following into your scenario to get started:
{{addDays(formatDate(3.`Current Date`; "MM/DD/YYYY"); 6 - formatDate(3.`Current Date`; "d"))}}
What This Scenario Should Look Like in Your Make.com Scenario:
addDaysformatDateYour Date Value;MM/DD/YYYY);6–formatDateYour Date Value;d))
Conclusion
In this enlightening piece, we learned a simple yet effective way to ‘Find the Start and End Dates’ of a week using any given date within that week. The key takeaways are how to calculate the number of days to subtract or add from the chosen date to derive both the Sunday and Saturday dates. This knowledge is handy when you need to determine the beginning and end of a week for any specific date. Remember, you can always visit the article author’s website for more information on the used formulas if you have a lingering uncertainty or curiosity.