You need to sign in to do that

Don't have an account?

diwoman

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

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)

## All Answers

BuellAre you just looking for a number, for example, week 32? diwomanYes, I just want a number, such as "32". The field name will say "Close Week". 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

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.

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)

diwoman

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

Cheers.

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)

diwomanPerfect...thanks again! diwomanWe'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! 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!!!

jbrewOh 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! BuellSo are you looking for the first week of next year to be counted as week 2 then? jbrewI'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. BuellAnd I'm guessing you want your weeks to start on Sundays? jbrewyes, that would be great Buell

This should take care of the wednesday's issue you were seeing too.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)

)

jbrew

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

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:

BuellAh, 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. 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) )

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

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