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?
Who is Participating?

Commented:
Try
=TODAY()-WEEKDAY(TODAY(),3)
regards, barry
0

Commented:
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

Commented:
=7*INT((TODAY()-2)/7)+2               'Switches every Monday. Tuesday through Sunday returns previous Monday.
0

Commented:
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

Commented:
I like Barry's tweak of Patrick's formula better than mine. As usual. :-)
0

Author 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

Commented:
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

Commented:
ISBTECH,=TODAY()                 returns current system date without time=NOW()                   returns current system date AND timePatrick
0

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

Author 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.