Solved

# Date Question!

Posted on 2011-09-07
168 Views
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.......
0
Question by:angelfromabove
• 3
• 2

LVL 50

Expert Comment

ID: 36497814
If the date is still in BN2 try this formula

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

regards, barry
0

LVL 50

Accepted Solution

barry houdini earned 500 total points
ID: 36497844
....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

LVL 51

Expert Comment

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

LVL 51

Expert Comment

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

LVL 51

Expert Comment

ID: 36497920
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 Comment

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

## Featured Post

Question has a verified solution.

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