Auto populate the date of "Monday" of the current week in Excel

Hi all,

Is there a way that I can use the today command in Excel to populate the date of Monday of the current week regardless of the day the user opens the spreadsheet?
ISBTECHAsked:
Who is Participating?
 
barry houdiniConnect With a Mentor Commented:
Try
=TODAY()-WEEKDAY(TODAY(),3)
regards, barry
0
 
Patrick MatthewsCommented:
Assuming that your "current week" begins on either Sunday or Monday, you can use a formula like this:=TODAY()+CHOOSE(WEEKDAY(TODAY()),1,0,-1,-2,-3,-4,-5)
0
 
byundtCommented:
=7*INT((TODAY()-2)/7)+2               'Switches every Monday. Tuesday through Sunday returns previous Monday.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
byundtCommented:
A shorter version of Patrick's formula uses the second parameter of WEEKDAY to eliminate the need to CHOOSE:
=TODAY()+1-WEEKDAY(TODAY(),2)
0
 
byundtCommented:
I like Barry's tweak of Patrick's formula better than mine. As usual. :-)
0
 
ISBTECHAuthor Commented:
Ok that works great but can you tell me why?  I can see it's checking different numbers of days from (today) but how are able to determine which day it's looking for?
0
 
Patrick MatthewsCommented:
ISBTECH,TODAY() always returns the current system date, and WEEKDAY returns a day of week index.  The exact index returned depends on what is passed for the second argument.  By default, if you use 1 or omit the second argument, as I did, then Sun=1, Mon=2, ..., Sat=7.  If you use 2, a la Brad, then Mon=1, Tue=2, ..., Sun=7.  If you use 3, like barry, then Mon=0, Tue=1, ..., Sun=6.I was being kind of lazy in using CHOOSE :)Patrick
0
 
Patrick MatthewsCommented:
ISBTECH,=TODAY()                 returns current system date without time=NOW()                   returns current system date AND timePatrick
0
 
Patrick MatthewsConnect With a Mentor Commented:
I think I like barry's best of all, as usual.  When it comes down to a formula battle, going up against barry is just futile :)
0
 
ISBTECHAuthor Commented:
Thanks all,  I'm splitting the credit because while I'm using Barry's formula Patrick helped me understand it.

Thanks again.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.