Apex Code Development You need to sign in to do that
Don't have an account?
Angela Mullen-Smith 20 I need to extract the fiscal month from the Close date field
Hi
I have set up my Fiscal year and I know need to create a field that shows which month the date relates to ie we use a 4-4-5 calendar and Nov 26th 2016 for example falls into Dec so for reporting purposes I need to highight this?
I have set up my Fiscal year and I know need to create a field that shows which month the date relates to ie we use a 4-4-5 calendar and Nov 26th 2016 for example falls into Dec so for reporting purposes I need to highight this?
After some research, I found below links which have some formulas for the same please refer those links it can help you to start over.
https://success.salesforce.com/answers?id=90630000000giT4AAI
https://success.salesforce.com/answers?id=90630000000h1S1AAI
https://success.salesforce.com/answers?id=90630000000gvlmAAA
Hope this helps you!
If this helps you please mark it as solved so that it will be available for others as a proper solution.
Thanks and Regards
Sandhya
Thanks for this - I did have a look and they didn't quite match what I need and this is what I created - its not dynamic but does the job.
It works and I hope it can benefit other people.
IF(
AND(
CloseDate >= Date(2016 ,01, 01),
CloseDate <= Date(2016, 01, 29)), "January 2016",
IF(
AND(
CloseDate >= Date(2016, 01, 30),
CloseDate <= Date(2016, 02, 26)), "February 2016",
IF(
AND(
CloseDate >= Date(2016, 02, 27),
CloseDate <= Date(2016, 04, 01)), "March 2016",
IF(
AND(
CloseDate >= Date(2016, 04, 02),
CloseDate <= Date(2016, 04, 29)), "April 2016",
IF(
AND(
CloseDate >= Date(2016, 04, 30),
CloseDate <= Date(2016, 05, 27)), "May 2016",
IF(
AND(
CloseDate >= Date(2016, 05, 28),
CloseDate <= Date(2016, 07, 01)), "June 2016",
IF(
AND(
CloseDate >= Date(2016, 07, 02),
CloseDate <= Date(2016, 07, 29)), "July 2016",
IF(
AND(
CloseDate >= Date(2016, 07, 30),
CloseDate <= Date(2016, 08, 26)), "August 2016",
IF(
AND(
CloseDate >= Date(2016, 08, 27),
CloseDate <= Date(2016, 09, 30)), "September 2016",
IF(
AND(
CloseDate >= Date(2016, 10, 01),
CloseDate <= Date(2016, 10, 28)), "October 2016",
IF(
AND(
CloseDate >= Date(2016, 10, 29),
CloseDate <= Date(2016, 11, 25)), "November 2016",
IF(
AND(
CloseDate >= Date(2016, 11, 26),
CloseDate <= Date(2016, 12, 31)), "December 2016",
IF(
AND(
CloseDate >= Date(2017 ,01, 01),
CloseDate <= Date(2017, 01, 27)), "January 2017",
IF(
AND(
CloseDate >= Date(2017, 01, 28),
CloseDate <= Date(2017, 02, 24)), "February 2017",
IF(
AND(
CloseDate >= Date(2017, 02, 25),
CloseDate <= Date(2017, 03, 31)), "March 2017",
IF(
AND(
CloseDate >= Date(2017, 04, 01),
CloseDate <= Date(2017, 04, 28)), "April 2017",
IF(
AND(
CloseDate >= Date(2017, 04, 29),
CloseDate <= Date(2017, 05, 26)), "May 2017",
IF(
AND(
CloseDate >= Date(2017, 05, 27),
CloseDate <= Date(2017, 06, 30)), "June 2017",
IF(
AND(
CloseDate >= Date(2017, 07, 01),
CloseDate <= Date(2017, 07, 28)), "July 2017",
IF(
AND(
CloseDate >= Date(2017, 07, 29),
CloseDate <= Date(2017, 08, 25)), "August 2017",
IF(
AND(
CloseDate >= Date(2017, 08, 26),
CloseDate <= Date(2017, 09, 29)), "September 2017",
IF(
AND(
CloseDate >= Date(2017, 09, 30),
CloseDate <= Date(2017, 10, 27)), "October 2017",
IF(
AND(
CloseDate >= Date(2017, 10, 28),
CloseDate <= Date(2017, 11, 24)), "November 2017",
IF(
AND(
CloseDate >= Date(2017, 11, 25),
CloseDate <= Date(2017, 12, 31)), "December 2017",
" "))))))))))))))))))))))))
This was the most sustainable way I found to return Fiscal Month, Fiscal Quarter, and Fiscal Year. (We use a 5-4-4 fiscal calendar)
Resolution:
Process Builder on Subscription Creation:
IF( Today() < rDate && Today() > xDate) Set Fiscal Year Start Date: xx/xx/2020 & Set Fiscal Year: 2020
IF( Today() < yDate && Today() > zDate) Set Fiscal Year Start Date: xx/xx/2021 & Set Fiscal Year: 2021
ETC.....
(Side note: If you have subscriptions longer than 1 year you will need the Fiscal Year formula at the end)
Fiscal Week:
CEILING( (Today() - Fiscal_Year_Start_Date__c)/7)
Fiscal Month:
IF( Fiscal_Week__c <= 5, 1,
IF(Fiscal_Week__c <= 9, 2,
IF(Fiscal_Week__c <= 13, 3,
IF(Fiscal_Week__c <= 18, 4,
IF(Fiscal_Week__c <= 22, 5,
IF(Fiscal_Week__c <= 26, 6,
IF(Fiscal_Week__c <= 31, 7,
IF(Fiscal_Week__c <= 35, 8,
IF(Fiscal_Week__c <= 39, 9,
IF(Fiscal_Week__c <= 44, 10,
IF(Fiscal_Week__c <= 48, 11,
12)
))))))))))
Fiscal Quarter: (had to reference fiscal week instead of month due to compile size issue)
IF( Fiscal_Week__c <= 13, 1,
IF( Fiscal_Week__c <= 26, 2,
IF( Fiscal_Week__c <= 39, 3, 4)
))
Fiscal Year: (longer than 1 year subscriptions)
IF( Today <= xx/xx/2020, 2020,
IF( Today <= xx/xx/2021, 2021,
IF( Today <= xx/xx/2022, 2022,
ETC....
))