function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
GRStevenBrookesGRStevenBrookes 

Calculating Week number in month.

Hi,

 

is there a way of calculating the week in the month (i.e Week 1-5) that an opportunity is marked as closed won.

I gather a formula field is the best way - but cant work out how it would be done?

 

Thanks in advance for your help.

Ankit AroraAnkit Arora

Not sure but please let me know if this satisfies your requirement :

 

IF(DAY(TODAY()) / 7 < 1 , 1 , IF(DAY(TODAY()) / 7 < 2 , 2 , IF(DAY(TODAY()) / 7 < 3 , 3 , IF(DAY(TODAY()) / 7 < 4 , 4 , 0))))

Replace TODAY() with your date field.

 

Thanks

Ankit Arora

Blog | Facebook | Blog Page
GRStevenBrookesGRStevenBrookes

Hi,

 

No unfortunatly this does not return the correct data - for example, for an opportunity with a close date of 23/07/2011 it is showing a value of 2 when infact the 23/7 was week 4.

 

I wonder however ,if their is a way of applying the same logic??

 

Thanks anyhow.

 

Steve
Ankit AroraAnkit Arora

This formula :

 

IF(DAY( ankit__Start_Date__c ) / 7 < 1 , 1 , IF(DAY(ankit__Start_Date__c) / 7 < 2 , 2 , IF(DAY(ankit__Start_Date__c) / 7 < 3 , 3 , IF(DAY(ankit__Start_Date__c) / 7 < 4 , 4 , IF(DAY(ankit__Start_Date__c) / 7 < 5 , 5 , 0) ))))

Is working for me, just wondering if you verify again that date is in format MM/DD/yyyy i.e 07/23/2011 and not 23/07/2011 .

 

I have entered 07/23/2011 and it is giving me 4 as expected.

 

Thanks

Ankit Arora

Blog | Facebook | Blog Page
Jake GmerekJake Gmerek

Ankit,

 

wouldn't your formula fail for the 7th, 14th, etc?  7/7=1 and thus would return a value of two, but it should return a value of one.  I think you need to use something like:

 

IF(Ceiling(DAY( ankit__Start_Date__c ) / 7) = 1 , 1, ...

 

That should return the proper numbers.
sfostersfoster

Jake, a simple <= solves that problem.
Suzanne JukiewiczSuzanne Jukiewicz
Since everybody’s answer led me to the correct formula, I am posting the full correct response to the original question (week in the month).  The following will return the week number within the Month.  Replace CloseDate with your required reference date.

IF(DAY( CloseDate ) / 7 <= 1 , 1 , 
IF(DAY( CloseDate ) / 7 <= 2 , 2 , 
IF(DAY( CloseDate ) / 7 <= 3 , 3 , 
IF(DAY( CloseDate ) / 7 <= 4 , 4 , 
IF(DAY( CloseDate ) / 7 <= 5 , 5 , 0) ))))
Glyn Anderson (Slalom)Glyn Anderson (Slalom)
I think Jake was on to something.  How about just

CEILING( DAY( CloseDate ) / 7 )
Peter Thomson 3Peter Thomson 3
Count Days and Divide. Count the number of days in the month and divide thatnumber by 7, which is the number of days in one week. For example, if March has 31 days, there would be a total of 4.43 weeks in the month. (31 ÷ 7 = 4.43). More info Samsung complaints number (http://thecomplaintpoint.co.uk/samsung-complaints-email-phone-number)
Jef RaskinJef Raskin
Thanks in favor of sharing such a nice thinking, article is pleasant, thats why i have read it entirely. More at PSU Canvas 2019 Update (https://student-guides.com/psu-canvas/)