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
mw6mw6 

how to calculate number of weeks in the current month for salesforce formula

Hi 

Can any expert help me to create a formula to calculate the # of weeks in the current month ro create a formula field

Thanks in adv
Best Answer chosen by mw6
Akhil AnilAkhil Anil
Hello,

Why don't you simply tweak the formula like this ?
  
IF(MOD(DAY(Session_Start_Date__c ),7) == 0, 0, 1)+
FLOOR(DAY(Session_Start_Date__c )/7) +
FLOOR(((
CASE(MONTH(Session_Start_Date__c),
2, IF(OR( MOD( YEAR(TODAY() ), 400 ) = 0,AND( MOD( YEAR( TODAY() ), 4 ) = 0, MOD( YEAR( TODAY() ), 100 ) <> 0)), 29, 28),
4, 30,
6, 30,
9, 30,
11, 30,
31
))-DAY(Session_Start_Date__c ))/7)

That should do the trick !

Kindly mark it as an answer if that works !

All Answers

buggs sfdcbuggs sfdc
Hi Mw6,

please check with the below link 
https://success.salesforce.com/answers?id=90630000000h30dAAA

 
mw6mw6
Hi
Apologies, my question was not clear.  Currently I am using the below formula.  I need to calculate the # of weeks from the 'Session_Start_Date__c till to date. The work arround I am doing currently is change the  IF(MOD(YEAR(TODAY()),4)=0, 28, 28), line means change the 28 to 30 or 31.  I need help to fix this, so that no need to modify the formula every time.

IF(MOD(DAY(Session_Start_Date__c ),7) == 0, 0, 1)+
FLOOR(DAY(Session_Start_Date__c )/7) +
FLOOR(((
CASE(MONTH(Session_Start_Date__c),
2, IF(MOD(YEAR(TODAY()),4)=0, 28, 28),
4, 30,
6, 30,
9, 30,
11, 30,
31
))-DAY(Session_Start_Date__c ))/7)
 
Akhil AnilAkhil Anil
Hello,

Why don't you simply tweak the formula like this ?
  
IF(MOD(DAY(Session_Start_Date__c ),7) == 0, 0, 1)+
FLOOR(DAY(Session_Start_Date__c )/7) +
FLOOR(((
CASE(MONTH(Session_Start_Date__c),
2, IF(OR( MOD( YEAR(TODAY() ), 400 ) = 0,AND( MOD( YEAR( TODAY() ), 4 ) = 0, MOD( YEAR( TODAY() ), 100 ) <> 0)), 29, 28),
4, 30,
6, 30,
9, 30,
11, 30,
31
))-DAY(Session_Start_Date__c ))/7)

That should do the trick !

Kindly mark it as an answer if that works !
This was selected as the best answer
Srikanth Sandireddy 20Srikanth Sandireddy 20
CEILING(
    (
    DAY( Date__c ) +
    MOD( DATE( YEAR( Date__c ), MONTH( Date__c ), 1 ) - DATE( 1900, 4, 1 ), 7 )
    ) / 7
)