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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
All Courses

From novice to tech pro — start learning today.