function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
diwomandiwoman 

Calculating close Week based on opportunity close date

I am trying to create a formula to calculate the Week in the Year that an opportunity will close, based on the Close Date. 

 

There was a post earlier in April on the same subject, but it remains unsolved.  I have searched the help section and blogs, but cannot create the winning formula....any help is much appreciated!

Best Answer chosen by Admin (Salesforce Developers) 
BuellBuell

Already found an issue.  Here is an updated version of the formula.  Also, not sure what you are saying the issue is.  If you create a 'Number' formula field in the oppty and plug this in it will do the trick.

 

 

FLOOR(((CASE( MONTH( CloseDate ), 1,0, 2,31, 3,60, 4,91, 5,121, 6,152, 7,182, 8,213, 9,244, 10,274, 11,305, 12,335, 0)) - IF( MONTH( CloseDate) >= 3, (IF( MOD( YEAR( DATEVALUE(CreatedDate) ), 400) = 0, 0, IF( MOD( YEAR( DATEVALUE(CreatedDate) ), 100) = 0, 1, IF( MOD( YEAR( DATEVALUE(CreatedDate) ), 4) = 0, 0, 1)))), 0) + DAY( CloseDate )) / 7) + IF(MOD(((CASE( MONTH( CloseDate ), 1,0, 2,31, 3,60, 4,91, 5,121, 6,152, 7,182, 8,213, 9,244, 10,274, 11,305, 12,335, 0)) - IF( MONTH( CloseDate) >= 3, (IF( MOD( YEAR( DATEVALUE(CreatedDate) ), 400) = 0, 0, IF( MOD( YEAR( DATEVALUE(CreatedDate) ), 100) = 0, 1, IF( MOD( YEAR( DATEVALUE(CreatedDate) ), 4) = 0, 0, 1)))), 0) + DAY( CloseDate )), 7) = 0, 0,1)

 

 

 

All Answers

BuellBuell
Are you just looking for a number, for example, week 32?
diwomandiwoman
Yes, I just want a number, such as "32".  The field name will say "Close Week".
BuellBuell

It is ugly but ought to do the trick.  Will even account for leap years.

 

 

FLOOR(((CASE( MONTH( CloseDate ),
1,0,
2,31,
3,60,
4,91,
5,121,
6,152,
7,182,
8,213,
9,244,
10,274,
11,305,
12,335,
0)) - IF( MONTH( CloseDate) >= 3, (IF( MOD( YEAR( DATEVALUE(CreatedDate) ), 400) = 0, 0,
IF( MOD( YEAR( DATEVALUE(CreatedDate) ), 100) = 0, 1,
IF( MOD( YEAR( DATEVALUE(CreatedDate) ), 4) = 0, 0,
1)))), 0) + DAY( CloseDate )) / 7) + 1

 

 

 

Message Edited by Buell on 06-25-2009 11:10 AM
diwomandiwoman

Good grief, I was way off base with my attempts.

 

Many thanks...the formula has been accepted, but it does not calculate a value in the close week field based on the close date.  I've updated the close date on an existing opportunity and I've created a new one, but the field is not popluating the week number. 

 

I am looking for the same behavior of the Probability field and how it relates to the Stage field.

 

 

BuellBuell

Already found an issue.  Here is an updated version of the formula.  Also, not sure what you are saying the issue is.  If you create a 'Number' formula field in the oppty and plug this in it will do the trick.

 

 

FLOOR(((CASE( MONTH( CloseDate ), 1,0, 2,31, 3,60, 4,91, 5,121, 6,152, 7,182, 8,213, 9,244, 10,274, 11,305, 12,335, 0)) - IF( MONTH( CloseDate) >= 3, (IF( MOD( YEAR( DATEVALUE(CreatedDate) ), 400) = 0, 0, IF( MOD( YEAR( DATEVALUE(CreatedDate) ), 100) = 0, 1, IF( MOD( YEAR( DATEVALUE(CreatedDate) ), 4) = 0, 0, 1)))), 0) + DAY( CloseDate )) / 7) + IF(MOD(((CASE( MONTH( CloseDate ), 1,0, 2,31, 3,60, 4,91, 5,121, 6,152, 7,182, 8,213, 9,244, 10,274, 11,305, 12,335, 0)) - IF( MONTH( CloseDate) >= 3, (IF( MOD( YEAR( DATEVALUE(CreatedDate) ), 400) = 0, 0, IF( MOD( YEAR( DATEVALUE(CreatedDate) ), 100) = 0, 1, IF( MOD( YEAR( DATEVALUE(CreatedDate) ), 4) = 0, 0, 1)))), 0) + DAY( CloseDate )), 7) = 0, 0,1)

 

 

 

This was selected as the best answer
diwomandiwoman

It works!  Thank you...your effort is much appreciated! 

 

Cheers.

BuellBuell

Cleaned it up a bit.

 

 

((CloseDate - DATE(YEAR(CloseDate), 1, 1) + 1) / 7) + IF(MOD((CloseDate - DATE(YEAR(CloseDate), 1, 1) + 1), 7) = 0, 0, 1)

 

 

 

Message Edited by Buell on 06-26-2009 01:47 PM
diwomandiwoman
Perfect...thanks again!
diwomandiwoman
We've been using this formula and it's working well until we set a close date past 2009.  We set one for 2/25/2010 and a close week of 62 was calculated.  I reverted to the code that was posted earlier and that is calcuating properly.  Just thought I would mention it....thanks!
jbrewjbrew

I just found this posting and it is almost exactly what I need.  I have no idea how to read the formula itself but it works great for the most part.  Is there anything that I can add to it that takes into account a fiscal 52 week year?  For example, this year December 27 - 31 will end on week 53.  I want to make sure that these dates are accounted for as week 1 and not week 53. 

 

Thanks so much for the formula that has already been built.  Has been a life saver for me!!! 

jbrewjbrew
Oh also, I just noticed that it seems that it is miscalculating Wednesdays.  It reverts each Wednesday back to the week prior.  So for this week it calculates all days at week 34 except for Wednesday which it calculates as 33.  Thank you so much to anyone who is a formula expert!
BuellBuell
So are you looking for the first week of next year to be counted as week 2 then?
jbrewjbrew
I'm looking for the first full week to be week 2.  So for my example for the end of this year, I would want December 27 - January 2 to be considered week 1, Week 2 would be January 3 - 9, etc.
BuellBuell
And I'm guessing you want your weeks to start on Sundays?
jbrewjbrew
yes, that would be great
BuellBuell

It's not pretty but it works.

 

 

  CALCULATING WORK WEEK IN YEAR (with weeks running sun. - sat. and the 53rd week of the year counting as the 1st of the following year...)

IF(
((CloseDate - DATE(YEAR(CloseDate), 1, 1) + (CASE( MOD( DATE( YEAR( CloseDate ), 01, 01 ) - DATE(1900, 1, 7), 7),
0, + 1,
6, + 7,
2, + 3,
4, + 5,
5, + 6,
1, + 2,
+ 4))) / 7) + IF(MOD((CloseDate - DATE(YEAR(CloseDate), 1, 1) + (CASE( MOD( DATE( YEAR( CloseDate ), 01, 01 ) - DATE(1900, 1, 7), 7),
0, + 1,
6, + 7,
2, + 3,
4, + 5,
5, + 6,
1, + 2,
+ 4))), 7) = 0, 0, 1)

>= 53, 1,

((CloseDate - DATE(YEAR(CloseDate), 1, 1) + (CASE( MOD( DATE( YEAR( CloseDate ), 01, 01 ) - DATE(1900, 1, 7), 7),
0, + 1,
6, + 7,
2, + 3,
4, + 5,
5, + 6,
1, + 2,
+ 4))) / 7) + IF(MOD((CloseDate - DATE(YEAR(CloseDate), 1, 1) + (CASE( MOD( DATE( YEAR( CloseDate ), 01, 01 ) - DATE(1900, 1, 7), 7),
0, + 1,
6, + 7,
2, + 3,
4, + 5,
5, + 6,
1, + 2,
+ 4))), 7) = 0, 0, 1)
)

 

 This should take care of the wednesday's issue you were seeing too.
Message Edited by Buell on 08-20-2009 05:09 PM
jbrewjbrew

This is very close...

 

It is still calculating a 53 week year though and the weird part is that it seems to now be starting it's new week on Wednesdays rather than on Sundays.  Saturdays seem to be an issue too.  We don't typically have weekend dates but every once in a while it comes up.  Thank you again for your help with this.

 

Here's an excerpt that I pulled to help illustrate what's happening:

 

Tue, Dec 16, 2008       51Wed, Dec 17, 2008     52Thur, Dec 18, 2008      52Fri, Dec 19, 2008         52Sat, Dec 20, 2008        51Sun, Dec 21, 2008       52Mon, Dec 22, 2008      52Tue, Dec 23, 2008       52Wed, Dec 24, 2008     53Fri, Dec 26, 2008         53Mon, Dec 29, 2008      1Tue, Dec 30, 2008       1Wed, Dec 31, 2008     1Fri, Jan 02, 2009          2Sun, Jan 04, 2009        2Mon, Jan 05, 2009       2Tue, Jan 06, 2009        2Wed, Jan 07, 2009       3Thur, Jan 08, 2009       3Fri, Jan 09, 2009          3Mon, Jan 12, 2009       3Tue, Jan 13, 2009        3Wed, Jan 14, 2009       4Thur, Jan 15, 2009       4Fri, Jan 16, 2009          4Sat, Jan 17, 2009         3Mon, Jan 19, 2009       4Tue, Jan 20, 2009        4Wed, Jan 21, 2009       5Thur, Jan 22, 2009       5Fri, Jan 23, 2009          5Sun, Jan 25, 2009        5Mon, Jan 26, 2009       5Tue, Jan 27, 2009        5

 

BuellBuell
Ah, I see the problem.  When testing I'm showing the number field with 2 decimal places which will calculate things out correctly.  When you tell it to show no decimal places SFDC is kind enough to round things up for you...  You'll either have to show 2 decimal places and ignore them or rewrite the formula as adding FLOOR() will cause it to compile too large.
BuellBuell

Actually, give this one a go, set it up as you did before, a number formula field with 0 deimal places.

 

 

IF( ((CloseDate - DATE(YEAR(CloseDate), 1, 1) + MOD( DATE( YEAR( CloseDate ), 01, 01 ) - DATE(1900, 1, 7), 7) + 1 ) / 7) + IF(MOD((CloseDate - DATE(YEAR(CloseDate), 1, 1) + MOD( DATE( YEAR( CloseDate ), 01, 01 ) - DATE(1900, 1, 7), 7) + 1 ), 7) = 0, 0, 1) >= 53, 1, (FLOOR((CloseDate - DATE(YEAR(CloseDate), 1, 1) + MOD( DATE( YEAR( CloseDate ), 01, 01 ) - DATE(1900, 1, 7), 7) + 1 ) / 7)) + IF(MOD((CloseDate - DATE(YEAR(CloseDate), 1, 1) + MOD( DATE( YEAR( CloseDate ), 01, 01 ) - DATE(1900, 1, 7), 7) + 1 ), 7) = 0, 0, 1) )

 

 

 

jbrewjbrew

This worked perfectly!!!  Thank you so much for all of your help!

JAW99JAW99
Very cool. Would love to see something that read "Week of (1/31/2010)" for example...