+ Start a Discussion
DatajunkieDatajunkie 

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:
 
AND(
          OR(
                  ISNEW(),
                  ISCHANGED( StageName ),
                  ISCHANGED( CloseDate)
                  ),
           IsClosed = true,
           TODAY()>DATE(2008,6,24),
           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?
 
Thanks!!


Message Edited by Datajunkie on 06-27-2008 10:37 AM