The other day I needed to get the Monday of the current week so I can display “Week of the X” where X would be the date of that week’s Monday’s date. I figured this would be useful for you as well and I wanted to share this with you.
Building up the Formula
I knew that I could get the current date with the =Today() function. So let’s work with that.
While this post will get old over time, let’s just say that today’s date is 7/7/2017, which is a Friday. Our goal is to return 7/3/2017, which is the Monday of that week.
=Today() = 7/7/2017
Now how do we get the Monday of that current week?
Well, we need to move backward a specific number of days (or none if today is already a Monday). For 7/7/2017, we need to move back 4 days. However, when it becomes 7/8/2017, we need to move back 5 days. This formula needs to be able to pick the Monday of the week regardless of what day it is in that week.
The Weekday Function
We can use the Weekday function as a way to determine how far away today’s date is from the Monday of that week.
The Weekday() function returns the day of the week in terms of a number, where:
Sunday = 1 Monday = 2 Tuesday = 3 Wednesday = 4 Thursday = 5 Friday = 6 Saturday = 7
In our example, where today’s fictional date would be 7/7/2017, which is a Friday, equating to 6.
=Weekday(Today()) = 6
However, there is something consistent about this that we can use. If we subtract the Weekday() value from the date we’re looking at, we’ll ALWAYS end up on the previous Saturday.
=Today()-Weekday(Today()) =#7/7/2017#-Weekday(#7/7/2017#) =#7/7/2017#-6 =#7/1/2017# 7/1/2017
If today were 7/6/2016, the Weekday() function would return 5 because it is a Thursday. If we re-run our formula, it would be:
=Today()-Weekday(Today()) =#7/6/2017#-Weekday(#7/6/2017#) =#7/6/2017#-5 =#7/1/2017# 7/1/2017
With this info, we know that from the previous Saturday, we just have to move 2 days forward. All we have to do to the formula is add 2:
Re-running this through our 7/7/2017 example:
=Today()-Weekday(Today())+2 =#7/7/2017#-Weekday(#7/7/2017#)+2 =#7/7/2017#-6+2 =#7/7/2017#-4 =#7/3/2017# 7/3/2017
And that does it. If you want to get the Monday for a fixed date, just replace Today() with the date that you want. If you have a date in A2 that you would like to use, simply use:
What if I want to get a day other than Monday?
Simply change the +2 to a different value, depending on which day of the week you want to return:
+1 = Sunday +2 = Monday +3 = Tuesday +4 = Wednesday +5 = Thursday +6 = Friday +7 = Saturday
And there you have it. It’s pretty straight forward when we break this down into simpler parts and build up from there. If you have your own version of this or would like to share any improvement, please mention it in the comments below!
Are you ready to master Excel Dashboards?
Still not convinced? Check out my review of the course!
Suggest the next post!
I would love your help to know what I should post about next, so if you enjoyed this content and would like to see more, please let me know what you'd like me to talk about in the comments below. Thank you!