Don't have an account?

Browse by Topic

+ Start a Discussion

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

January 3, 2018
·
Like
0
·
Follow
2

Best Answer chosen by GMASJ

Arpit 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

January 8, 2018
·
Like
1
·
Dislike
0

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

January 3, 2018
·
Like
0
·
Dislike
0

GMASJ
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

January 3, 2018
·
Like
0
·
Dislike
0

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

January 3, 2018
·
Like
0
·
Dislike
0

Kushi
Please let me know if it worked

January 3, 2018
·
Like
0
·
Dislike
0

GMASJ
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

January 3, 2018
·
Like
0
·
Dislike
0

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

January 4, 2018
·
Like
0
·
Dislike
0

GMASJ
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

Thanks
Sudhir

January 4, 2018
·
Like
0
·
Dislike
0

GMASJ
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

January 4, 2018
·
Like
0
·
Dislike
0

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

January 5, 2018
·
Like
0
·
Dislike
0

GMASJ
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

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

January 5, 2018
·
Like
0
·
Dislike
0

Arpit 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

January 5, 2018
·
Like
0
·
Dislike
0

GMASJ
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

January 5, 2018
·
Like
0
·
Dislike
0

Arpit 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

January 5, 2018
·
Like
0
·
Dislike
0

GMASJ
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

January 5, 2018
·
Like
0
·
Dislike
0

Arpit 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

January 5, 2018
·
Like
0
·
Dislike
0

GMASJ
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

January 5, 2018
·
Like
0
·
Dislike
0

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

January 7, 2018
·
Like
0
·
Dislike
0

GMASJ
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

January 7, 2018
·
Like
0
·
Dislike
0

This was selected as the best answer

GMASJ
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

January 8, 2018
·
Like
0
·
Dislike
0

Arpit Jain7
Glad it Helped !!! :)

January 8, 2018
·
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

Fiscal Quarter + Week based on a date

All Answers

You need to sign in to do that.