Don't have an account?

Browse by Topic

+ Start a Discussion

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!

June 25, 2009
·
Like
0
·
Follow
0

Best Answer chosen by Admin (Salesforce Developers)

Buell
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)

June 25, 2009
·
Like
0
·
Dislike
0

Buell
Are you just looking for a number, for example, week 32?

June 25, 2009
·
Like
0
·
Dislike
0

diwoman
Yes, I just want a number, such as "32". The field name will say "Close Week".

June 25, 2009
·
Like
0
·
Dislike
0

Buell
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

June 25, 2009
·
Like
0
·
Dislike
0

diwoman
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.

June 25, 2009
·
Like
0
·
Dislike
0

This was selected as the best answer

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

Cheers.

June 25, 2009
·
Like
0
·
Dislike
0

Buell
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

June 26, 2009
·
Like
0
·
Dislike
0

diwoman
Perfect...thanks again!

June 29, 2009
·
Like
0
·
Dislike
0

diwoman
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!

July 17, 2009
·
Like
0
·
Dislike
0

jbrew
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!!!

August 20, 2009
·
Like
0
·
Dislike
0

jbrew
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!

August 20, 2009
·
Like
0
·
Dislike
0

Buell
So are you looking for the first week of next year to be counted as week 2 then?

August 20, 2009
·
Like
0
·
Dislike
0

jbrew
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.

August 20, 2009
·
Like
0
·
Dislike
0

Buell
And I'm guessing you want your weeks to start on Sundays?

August 20, 2009
·
Like
0
·
Dislike
0

jbrew
yes, that would be great

August 20, 2009
·
Like
0
·
Dislike
0

Buell
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

August 20, 2009
·
Like
0
·
Dislike
0

jbrew
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

August 21, 2009
·
Like
0
·
Dislike
0

Buell
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.

August 24, 2009
·
Like
0
·
Dislike
0

Buell
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) )

August 24, 2009
·
Like
0
·
Dislike
0

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

August 24, 2009
·
Like
0
·
Dislike
0

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

February 2, 2010
·
Like
1
·
Dislike
0

You need to sign in to do that.

Need an account? Sign Up

Have an account? Sign In

Dismiss

Browse by Topic

Welcome to Support!

Show

sorted by

Calculating close Week based on opportunity close date

All Answers

You need to sign in to do that.