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
MaheemSamMaheemSam 

Display Week Number Based on date quarter

Hi,
  
   I want to display the week number based  on quartrly of the year Please suggest me how to display
 
   Below is the image how it should be displayed
   User-added image

I wrote a formula which is giving quarter correctly but the week number is not displayed correclty
CASE(MONTH( CloseDate ),1,"Q4",2,"Q1",3,"Q1",4,"Q1",5,"Q2",6,"Q2",7,"Q2",8,"Q3",9,"Q3",10, "Q3",11,"Q4",12,"Q4","None")& "-W"&Text(CEILING( 
( 
DAY( CloseDate ) + 
MOD( DATE( YEAR( CloseDate ), MONTH( CloseDate ), 1 ) - DATE( 1900, 4, 1 ), 7 ) 
) / 7 
))

I need to display the weekly based on the image attached Please suggest me how to modifiy the formula.

Thanks
Sudhir
Best Answer chosen by MaheemSam
Arpit Jain7Arpit Jain7
Hi Sudhir,

It seems duplicate question similar to below

https://developer.salesforce.com/forums/ForumsMain?id=9060G0000005PAMQA2

Suggestion provided there

Try using below approach

Create two formula field

First formula Field (Return Type Number) : IF( 
CEILING( ( CloseDate - DATE( YEAR( CloseDate ), 1, 1) + 1) / 7) > 52, 52, 
IF( AND( Month(CloseDate ) >= 2,Month(CloseDate ) < 5), CEILING( ( CloseDate - DATE( YEAR( CloseDate ), 2, 1) + 1) / 7), 
IF(AND( Month(CloseDate ) >= 5,Month(CloseDate ) < 8), CEILING( ( CloseDate - DATE( YEAR( CloseDate ), 5, 1) + 1) / 7), 
IF(AND( Month(CloseDate ) >= 8,Month(CloseDate ) < 11), CEILING( ( CloseDate - DATE( YEAR( CloseDate ), 8, 1) + 1) / 7), 
CEILING( ( CloseDate - DATE( YEAR( CloseDate ), 11, 1) + 1) / 7)))))

Second formula Field (Return Type Number): 
IF(Month(SLAExpirationDate__c ) <2, CEILING( ( SLAExpirationDate__c - DATE( YEAR( SLAExpirationDate__c ), 1, 1) + 1) / 7)+CEILING( ( DATE( YEAR( SLAExpirationDate__c ), 12, 31) - DATE( YEAR( SLAExpirationDate__c ), 11, 1) + 1) / 7),0)

Then create third text field and use Workflow rule to populate corrcet value in that third field

CASE(MONTH( CloseDate ),1,"Q4",2,"Q1",3,"Q1",4,"Q1",5,"Q2",6,"Q2",7,"Q2",8,"Q3",9,"Q3",10, "Q3",11,"Q4",12,"Q4","None")& "-W"&IF(Month(CloseDate ) >=2,Text( First_Field__c ),Text( Second_Field__c ))

Thanks
Arpit

All Answers

Saravana Muthu 8Saravana Muthu 8
Hi,

Please see if the below formula helps.

I have taken it from the below link.

https://help.salesforce.com/articleView?id=000249334&type=1

MOD(FLOOR( (DATEVALUE(CreatedDate) - DATEVALUE("2006-01-01" ))/7),52)+1

Thanks,
Saravana
MaheemSamMaheemSam
Thanks Saravana for you reply but the request is I need week number quarterly this is giving at year Please let me know if you have any suggestion.

Thanks
Sudhir
Saravana Muthu 8Saravana Muthu 8
Please see if the below link if it helps.

https://www.justinsilver.com/technology/salesforce/get-week-month-salesforce-formula-field/

Thanks,
Saravana
Saravana Muthu 8Saravana Muthu 8
Please also see this video if it helps.

https://m.youtube.com/watch?v=HlXHC2pvp30

Thanks,
Saravana
 
Arpit Jain7Arpit Jain7
Hi Sudhir,

It seems duplicate question similar to below

https://developer.salesforce.com/forums/ForumsMain?id=9060G0000005PAMQA2

Suggestion provided there

Try using below approach

Create two formula field

First formula Field (Return Type Number) : IF( 
CEILING( ( CloseDate - DATE( YEAR( CloseDate ), 1, 1) + 1) / 7) > 52, 52, 
IF( AND( Month(CloseDate ) >= 2,Month(CloseDate ) < 5), CEILING( ( CloseDate - DATE( YEAR( CloseDate ), 2, 1) + 1) / 7), 
IF(AND( Month(CloseDate ) >= 5,Month(CloseDate ) < 8), CEILING( ( CloseDate - DATE( YEAR( CloseDate ), 5, 1) + 1) / 7), 
IF(AND( Month(CloseDate ) >= 8,Month(CloseDate ) < 11), CEILING( ( CloseDate - DATE( YEAR( CloseDate ), 8, 1) + 1) / 7), 
CEILING( ( CloseDate - DATE( YEAR( CloseDate ), 11, 1) + 1) / 7)))))

Second formula Field (Return Type Number): 
IF(Month(SLAExpirationDate__c ) <2, CEILING( ( SLAExpirationDate__c - DATE( YEAR( SLAExpirationDate__c ), 1, 1) + 1) / 7)+CEILING( ( DATE( YEAR( SLAExpirationDate__c ), 12, 31) - DATE( YEAR( SLAExpirationDate__c ), 11, 1) + 1) / 7),0)

Then create third text field and use Workflow rule to populate corrcet value in that third field

CASE(MONTH( CloseDate ),1,"Q4",2,"Q1",3,"Q1",4,"Q1",5,"Q2",6,"Q2",7,"Q2",8,"Q3",9,"Q3",10, "Q3",11,"Q4",12,"Q4","None")& "-W"&IF(Month(CloseDate ) >=2,Text( First_Field__c ),Text( Second_Field__c ))

Thanks
Arpit
This was selected as the best answer
Davide GammoneDavide Gammone
I have post an Idea for that problem!

Formula Function ISOWEEKNUM

https://trailblazer.salesforce.com/ideaView?id=0874V0000003lvEQAQ

Thanks
Davide