 ShowAll Questionssorted byDate Posted MaheemSam

# 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 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 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 Saravana 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 MaheemSam
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 8

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

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

Thanks,
Saravana Arpit 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 Gammone
I have post an Idea for that problem!

Formula Function ISOWEEKNUM

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

Thanks
Davide