Solved

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

Posted on 2010-08-25
10
536 Views
Last Modified: 2012-10-22
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
Comment
Question by:ISBTECH
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
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

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

Accepted Solution

by:
barry houdini earned 250 total points
ID: 33525277
Try
=TODAY()-WEEKDAY(TODAY(),3)
regards, barry
0
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
LVL 81

Expert Comment

by:byundt
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

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

Author Comment

by:ISBTECH
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 92

Expert Comment

by:Patrick Matthews
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 92

Expert Comment

by:Patrick Matthews
ID: 33525766
ISBTECH,=TODAY()                 returns current system date without time=NOW()                   returns current system date AND timePatrick
0
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 250 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

by:ISBTECH
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
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…

774 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question