You need to sign in to do that

Don't have an account?

GMASJ

# 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

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

Arpit Jain7Hi 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

KushiHi 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")

GMASJHi 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

KushiSimilar 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")

KushiPlease let me know if it worked GMASJHi 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 Jain7Hi 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.GMASJThanks 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

Thanks

Sudhir

GMASJHi 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.

Thanks

Sudhir

Arpit Jain7Hi 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.GMASJHi 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

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 Jain7I 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

GMASJThanks 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 Jain7Try 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

GMASJMay 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 Jain7No 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

GMASJCan 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 Jain7Sudhir, 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... GMASJHi 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 Jain7Hi Sudhir

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

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,

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

GMASJHi 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 Jain7Glad it Helped !!! :)