Go Premium for a chance to win a PS4. Enter to Win

x
Solved

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

Posted on 2010-08-25
Medium Priority
582 Views
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?
0
Question by:ISBTECH
• 4
• 3
• 2
• +1

LVL 93

Expert Comment

ID: 33525066
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

LVL 81

Expert Comment

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

LVL 50

Accepted Solution

barry houdini earned 1000 total points
ID: 33525277
Try
=TODAY()-WEEKDAY(TODAY(),3)
regards, barry
0

LVL 81

Expert Comment

ID: 33525292
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

LVL 81

Expert Comment

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

Author Comment

ID: 33525662
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

LVL 93

Expert Comment

ID: 33525750
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

LVL 93

Expert Comment

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

LVL 93

Assisted Solution

Patrick Matthews earned 1000 total points
ID: 33525787
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 Closing Comment

ID: 33525929
Thanks all,  I'm splitting the credit because while I'm using Barry's formula Patrick helped me understand it.

Thanks again.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand â€“ and conveys the hard lessons his company learned in the aftermath.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templaâ€¦
###### Suggested Courses
Course of the Month8 days, 19 hours left to enroll