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

January 5, 2018
·
Like
0
·
Follow
2

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

January 8, 2018
·
Like
1
·
Dislike
0

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

January 7, 2018
·
Like
0
·
Dislike
0

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

January 7, 2018
·
Like
0
·
Dislike
0

Saravana 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

January 8, 2018
·
Like
0
·
Dislike
0

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

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

Thanks,
Saravana

January 8, 2018
·
Like
0
·
Dislike
0

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

March 18, 2021
·
Like
0
·
Dislike
0

You need to sign in to do that.

Need an account? Sign Up

Have an account? Sign In

Dismiss

Browse by Topic

Welcome to Support!

Show

sorted by

Display Week Number Based on date quarter

All Answers

You need to sign in to do that.