Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 179
  • Last Modified:

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.......
0
angelfromabove
Asked:
angelfromabove
  • 3
  • 2
1 Solution
 
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
 
barry houdiniCommented:
....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
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now