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.......
angelfromaboveAsked:
Who is Participating?
 
barry houdiniConnect With a Mentor 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
 
barry houdiniCommented:
If the date is still in BN2 try this formula

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

regards, barry
0
 
HainKurtSr. 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

Open in new window

0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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

Open in new window

0
 
HainKurtSr. 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

Open in new window

0
 
angelfromaboveAuthor 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.

All Courses

From novice to tech pro — start learning today.