function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Crystal ReganCrystal Regan 

Quarter End Date Calculation

Hello,
Trying to get a calc for Quarter End Date, but coming up short. The formula below comes up close, except it puts January in an April Close. See screenshot for results of this formula.

The Quarters Align as such:
  • 4/30/Same Year - Feb, Mar, Apr
  • 7/31/Same Year - May, Jun, Jul
  • 10/31/Same Year - Aug, Sep, Oct
  • 01/31/Next Year - Nov, Dec, Jan
Formula:
CASE(CEILING(MONTH(CloseDate) / 3),
1, DATE(YEAR(CloseDate), 05, 01) - 1,
2, DATE(YEAR(CloseDate), 08, 01) - 1,
3, DATE(YEAR(CloseDate), 11, 01) - 1,
4, DATE(YEAR(CloseDate) + 1, 02, 01) - 1,
NULL
)

Results of Current Formula:
User-added image
SubratSubrat (Salesforce Developers) 
Hello Crystal ,

Can you try with the below formula and let me know further : 
CASE(
    CEILING(MONTH(CloseDate) / 3),
    1, DATE(YEAR(CloseDate), 4, 30),
    2, DATE(YEAR(CloseDate), 7, 31),
    3, DATE(YEAR(CloseDate), 10, 31),
    4, DATE(YEAR(CloseDate) + 1, 1, 31),
    NULL
)

This formula assumes the year is the same as the CloseDate's year for the first three quarters and increments the year by 1 for the fourth quarter.

Make sure to test this formula with different CloseDates to ensure it produces the expected results.

If this helps , please mark this as Best Answer.
Thank you.