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

Close date validation rules using Custom Fiscal Years

I need some help finding a "best practices" kind of formula to handle this. We are changing to using a custom fiscal year (based on 4.4.5) The idea is that 3 days after the close of the fiscal month, you can no longer close a sale in that prior month. On the 4th day after the month closes, we run reports, and we can't have anyone going back and making entries in a month that's already been reported to the public. (Much like the way Accounting has to close each month. with no retroactive entries)
Example:  Fiscal June (2008) ended June 21st.  The reps have until June 24th to update any opportunities as having closed in June. I need a validation rule that, on June 25th, if someone tried to close a sale in fiscal June (or earlier), it will error out and say, "You've tried to close an opportunity during a month that is already closed. Please close this sale during the current month."
Before we moved to a custom fiscal year, I had a single formula that worked forever, using the MONTH() and DAY() functions. (Saying if it was greater than the 4th day of the month, the close date can't be less than the first day of the current month.) But those won't work for fiscal months.
Even trying to write a formula with the exact date ranges is giving me trouble. I can't get a single formula that can handle all 12 months in the year. I can't even get one that will handle two months, for that matter. I keep getting syntax errors. Too many OR and AND statements nested. (I can't say if it's a salesforce limitation or just my own problems with writing the formula!)
I could write 12 different validation rules for each year - one for each month. In that case, I'd write something like:
                  ISCHANGED( StageName ),
                  ISCHANGED( CloseDate)
           IsClosed = true,
           CloseDate <DATE(2008,6,22)
for each month (this one being June).
But I'd prefer to have it in one formula. Even something like this would have to be re-written every year, but I'd rather re-write one formula a year isntead of 12! So I'd rather have one formula that could handle it forever - like the one I could use when it was a Calendar Month.  But that may be asking for too much.
Any suggestions?

Message Edited by Datajunkie on 06-27-2008 10:37 AM
Scaifez achariahScaifez achariah
You can use the following formula as a starting point for your close date validation rule with custom fiscal years:
lessCopy code and find result today (
AND( CloseDate < DATE(YEAR(TODAY()), MONTH(TODAY()), 1), CloseDate > DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) - 2, 24) )
This formula checks if the close date is earlier than the first day of the current month and later than the 24th day of the previous fiscal month. It assumes that your fiscal year starts in a month other than January and that each fiscal month ends on the 21st.
Feel free to modify this formula according to your specific fiscal year settings. Let me know if you need further assistance!
Jenny BarriosJenny Barrios
No idea ....  view the Swertres result today ( you can stay up-to-date on the latest lotto results.
Naresh Kaneriya 9Naresh Kaneriya 9

Hi Datajunkie,

Here is my suggestion for a validation rule that should work for your scenario- 


    IsClosed = true,
    CloseDate < TODAY(),
    CASE(MOD(Month(CloseDate) - 5, 12),
        0, Day(CloseDate) > 21,
        1, Day(CloseDate) > 21,
        2, Day(CloseDate) > 23,
        3, Day(CloseDate) > 21,
        4, Day(CloseDate) > 22,
        5, Day(CloseDate) > 22,
        6, Day(CloseDate) > 23,
        7, Day(CloseDate) > 23,
        8, Day(CloseDate) > 23,
        9, Day(CloseDate) > 23,
        10, Day(CloseDate) > 23,
        11, Day(CloseDate) > 21,

This formula calculates whether the CloseDate is valid based on your fiscal year and month closing rules. It uses the MOD function to adjust the months to align with your fiscal year starting in June. The CASE function then checks the adjusted month and compares the day of the CloseDate to the appropriate day based on your rules.
This formula should work across different years as well, as it doesn't rely on hard-coded year values.

Please let me know if this solve your problem. Thanks

Shin ChaneShin Chane

It sounds like you're facing a complex challenge with the custom fiscal year transition. While it's tricky to find a one-size-fits-all formula, I'd suggest considering a dynamic approach. You might want to explore using custom settings or custom metadata types to store fiscal year start and end dates. This way, you can refer to these settings in your validation rule, making it adaptable to any fiscal year. It requires some setup, but it could save you from having to rewrite formulas every year. Good luck!  For more visit :