# Date Question!

I have this formula in a cell:
=IF(BN2="","","Qtr"&LOOKUP(MONTH(BN2),{1,4,7,10;2,3,4,1})&", "&YEAR(BN2+92)&", Week "&INT((BN2-DATE(YEAR(BN2+92)-1,10,1)-WEEKDAY(BN2-1))/7)+2)

It returns the following result (depending on the date): Qtr4, 2011, Week 48.

I would actually like a new formula which returns only the date for Monday of each new week: For example: "Week of September 5, 2011". This would encompass all of the days of that week, until the following Monday, so it would still read the same even if the dates were September 6, September 7, September 8, September 9, Septembe 10 or September 11. The formula would change again on Monday September 12, etc.......
###### Who is Participating?

Commented:
....and your current formula returns a blank if BN2 is blank so to include that too, just add an IF function, i.e.

=IF(BN2="","","Week of "&TEXT(BN2-WEEKDAY(BN2,3),"mmmm d, yyyy"))

barry
0

Commented:
If the date is still in BN2 try this formula

="Week of "&TEXT(BN2-WEEKDAY(BN2,3),"mmmm d, yyyy")

regards, barry
0

Sr. System AnalystCommented:
here it is:

A1: 07/09/2011
A2: =MondayOfDate(A1)

make sure A2 is formatted as date
``````Function MondayOfDate(dt)
MondayOfDate = DateAdd("d", -DatePart("w", dt) + 1, dt)
End Function
``````
0

Sr. System AnalystCommented:
oops, I guess we neeed to add 2 :)
``````Function MondayOfDate(dt)
MondayOfDate = DateAdd("d", -DatePart("w", dt) + 1, dt)
End Function
``````
0

Sr. System AnalystCommented:
sorry :) last post

A1: 07/09/2011
A2: =MondayOfDate(A1)

make sure A2 is formatted as date
``````Function MondayOfDate(dt)
MondayOfDate = DateAdd("d", -DatePart("w", dt) + 2, dt)
End Function
``````
0

Author Commented:
I will accept Barry's solution as the best, becaust it worked perfectly. Thanks to everyone else, however.  Barry, I still have an open question with you a week or two back, regarding past due date ranges; Can you please respond to that when you have a chance also?  Thanks so very much 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.