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
GMASJGMASJ 

Fiscal Quarter + Week based on a date

Hi, 

  I need to create a formual field based on a date which quater it belongs to and the week 

  Example : Date = 1-Jan-2018 I need a formula to display as Q4-W1
                   Date = 1-Feb-2018 I need a formula to display as Q1-W1
                   Date = 8-Feb-2018 I need a formula to display as Q1-W2
                   Date = 1-May-2018 I need a formula to display as Q2-W1
                   Date = 8-May-2018 I need a formula to display as Q2-W2

Please suggest me how to get this done in using salesforce formula.

Thanks
Sudhir
Best Answer chosen by GMASJ
Arpit Jain7Arpit Jain7
Hi Sudhir 

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

KushiKushi
Hi Sudhir,

Try this -

CASE(MONTH( Date), 
1, "Q1" , 
2, "Q1", 
3, "Q1", 
4, "Q2", 
5, "Q2", 
6, "Q2", 
7, "Q3", 
8, "Q3", 
9, "Q3", 
10, "Q4", 
11, "Q4", 
12, "Q4", 
"None") 

& "-" 

&CASE(YEAR(Date), 
2007, "2007" , 
2008, "2008", 
2009, "2009", 
2010, "2010", 
2011, "2011", 
2012, "2012", 
2013, "2013", 
2014, "2014", 
2015, "2015", 
2016, "2016", 
2017, "2017", 
2018, "2018", 
2019, "2019", 
2020, "2020", 
"None")
GMASJGMASJ
Hi Kushi, 

    I need the week not the year your display the quarter but not the week Please can you suggest how to get week 

Thanks
Sudhir
KushiKushi
Similar format. Just replace year with Day-

&CASE(Day(CloseDate), 
1,"W1" , 
2,"W1", 
3,"W1",
4,"W1",
5,"W1",
6,"W1",
7,"w1",
8,"w2" , 
"None")
KushiKushi
Please let me know if it worked
GMASJGMASJ
Hi Kushi, 

  Your first part of the formula is working second part of the week is not working 
 
  I need the week number as this below 

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


Please suggest me how to add

Thanks
Sudhir


 
Arpit Jain7Arpit Jain7
Hi Sudhir,
Try below formula

CASE(MONTH( SLAExpirationDate__c ),1,"Q1",2,"Q1",3,"Q1",4,"Q2",5,"Q2",6,"Q2",7,"Q3",8,"Q3",9,"Q3",10, "Q4",11,"Q4",12,"Q4","None")& "-W"&Text(CEILING( 

DAY( SLAExpirationDate__c ) + 
MOD( DATE( YEAR( SLAExpirationDate__c ), MONTH( SLAExpirationDate__c ), 1 ) - DATE( 1900, 4, 1 ), 7 ) 
) / 7 
))

Let me know for any issues.

Thanks
Arpit

Please mark this answer as SOLVED and BEST ANSWER if it helps you.
GMASJGMASJ
Thanks Arpit for formula and your help its not giving the correct week Please see the attached example I need some thing like below to display the formual Can you please help me with this Please suggest

User-added image

Thanks
Sudhir
GMASJGMASJ
Hi Arpith, 

  I made some changes to formula but week I am having a issue can you please help me week should display account to the image i attached in earlier thread.
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 
))

Thanks
Sudhir
Arpit Jain7Arpit Jain7
Hi Sudhir,

Try below formula if this fulfil your criteria

CASE(MONTH( CloseDate ),1,"Q1",2,"Q1",3,"Q1",4,"Q2",5,"Q2",6,"Q2",7,"Q3",8,"Q3",9,"Q3",10, "Q4",11,"Q4",12,"Q4","None")& "-W"&Text(IF( 
CEILING( ( CloseDate - DATE( YEAR( CloseDate ), 1, 1) + 1) / 7) > 52, 52, 
IF( Month(CloseDate )<4, CEILING( ( CloseDate - DATE( YEAR( CloseDate ), 1, 1) + 1) / 7), 
IF(AND( Month(CloseDate ) >= 4,Month(CloseDate ) < 7), CEILING( ( CloseDate - DATE( YEAR( CloseDate ), 4, 1) + 1) / 7), 
IF(AND( Month(CloseDate ) >= 7,Month(CloseDate ) < 10), CEILING( ( CloseDate - DATE( YEAR( CloseDate ), 7, 1) + 1) / 7), 
CEILING( ( CloseDate - DATE( YEAR( CloseDate ), 10, 1) + 1) / 7))))))

Let me know for any issues.

Thanks
Arpit

Please mark this answer as SOLVED and BEST ANSWER if it helps you.
 
GMASJGMASJ
Hi Arpith Thanks for you formula. 

When I select close date =  2/4/2018   it should display as Q1-W1 instead it is coming as Q1-W5

User-added image

 
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(IF( 
CEILING( ( CloseDate - DATE( YEAR( CloseDate ), 1, 1) + 1) / 7) > 52, 52, 
IF( Month(CloseDate )<4, CEILING( ( CloseDate - DATE( YEAR( CloseDate ), 1, 1) + 1) / 7), 
IF(AND( Month(CloseDate ) >= 4,Month(CloseDate ) < 7), CEILING( ( CloseDate - DATE( YEAR( CloseDate ), 4, 1) + 1) / 7), 
IF(AND( Month(CloseDate ) >= 7,Month(CloseDate ) < 10), CEILING( ( CloseDate - DATE( YEAR( CloseDate ), 7, 1) + 1) / 7), 
CEILING( ( CloseDate - DATE( YEAR( CloseDate ), 10, 1) + 1) / 7))))))

In above formula I made January as Q4 instead of Q1 can you please tune the week to display accoording Please suggest me

Thanks
Sudhir


 
Arpit Jain7Arpit Jain7
I modified below formula and it is working for all months except January..

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(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 ), 10, 1) + 1) / 7))))))

See if this could help
GMASJGMASJ
Thanks Arpith everything is working perfect except Q4 quarter it is not displaying as expected week number.

When I select closedate = 11/4/2018   it is displaying as Q4-W5 which is wrong it should return Q4-W1

Similary same issue for December and January rest other quarters are dispaying correctly. Please suggest. 

Thanks
Sudhir

  
Arpit Jain7Arpit Jain7
Try below one.. It will work for November and Dec as welll but not for January :(

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(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))))))

Thanks
Arpit
GMASJGMASJ
May be the Problem with January is it is getting shifted to next year 2019 

 When close date = 1/6/2019  what  it is displaying   Q4-W-43

I tested november and december it is working  any help and suggestion to fix the January Please try and let me know we fixed most of the formual till now. 

Thanks
Sudhir
Arpit Jain7Arpit Jain7
No Sudhir, I couldn't think any way for fixing January issue in formula field. If you feel your query is resolved you can select the formula which worked as best answer 

Thanks
Arpit
GMASJGMASJ
Can we hard code for just January month to achive this please let me know 

You fixed 90% of the issue I will surly mark it as answered Please suggest. 

Thanks
Sudhir
Arpit Jain7Arpit Jain7
Sudhir, I tried to add some logic in this formula field but now it is giving maximum character limit for one formula field, So I don't think formula will help further or you may be be need to shorten this formula logic. May be you can try to achieve the same with apex may be...
GMASJGMASJ
Hi Arpit, 

   Soory to trouble if your facing the character limit we can remove below piece of code. 
 
   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" 

   Please let me know if you have any other suggestion. 

Thanks
Sudhir
Arpit Jain7Arpit Jain7
Hi Sudhir 

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
GMASJGMASJ
Hi Arpit, 

  Your Genious resolved this issue I tried you method above it is working as expected. I am testing few other date will mark this as answered in my next reply 

Thanks
Sudhir
Arpit Jain7Arpit Jain7
Glad it Helped !!! :)