Validation Rule: No Holidays

Do you have a date field that should never allow holidays? Here’s one way to declaratively (through clicks only) alert the user that the date selected is a designated company holiday. (Thank you, James Garfield for helping me solve this!)

In this example, if a holiday is selected for the custom Delivery Date field, the error “This is a designated holiday. Select another date.” will display.

OR 
(Delivery_Date__c = Date(YEAR(TODAY()),1,1), 
Delivery_Date__c = Date(YEAR(TODAY()),4,18), 
Delivery_Date__c = Date(YEAR(TODAY()),5,26), 
Delivery_Date__c  = Date(YEAR(TODAY()),7,4), 
Delivery_Date__c  = Date(YEAR(TODAY()),9,1), 
Delivery_Date__c = Date(YEAR(TODAY()),11,27), 
Delivery_Date__c  = Date(YEAR(TODAY()),12,25))

Explanation of Dates
1,1 is January 1 (date never changes year after year)
4,18 is Good Friday (date changes from year-to-year)
5,26 is Memorial Day (date changes from year-to-year)
7,4 is July 4 (date never changes year after year)

One caveat, for the holidays where the actual date changes from year-to-year, you’ll need to modify the formula each January. Simply put a reminder on your calendar along with all the other things that require “maintenance” the beginning of each year. Tip: Copy/paste the URL in your calendar reminder for quick access. Also, include instructions in the Validation Rule description.

If you want to apply the same logic to a Date/Time field, here’s the formula that evaluates the date only and ignores the time value (thanks for the help Adam Marks!):

OR
(
DATEVALUE(Specific_Date_Time__c) = Date(YEAR(TODAY()),1,1),
DATEVALUE(Specific_Date_Time__c) = Date(YEAR(TODAY()),4,18),
DATEVALUE(Specific_Date_Time__c) = Date(YEAR(TODAY()),5,26),
DATEVALUE(Specific_Date_Time__c) = Date(YEAR(TODAY()),7,4),
DATEVALUE(Specific_Date_Time__c) = Date(YEAR(TODAY()),9,1),
DATEVALUE(Specific_Date_Time__c) = Date(YEAR(TODAY()),11,27),
DATEVALUE(Specific_Date_Time__c) = Date(YEAR(TODAY()),12,25))

Alternatively, you can reduce the character size of the formula and use this (thank you Deepak Anand for turning me on to this!):

CASE(
    TEXT(MONTH(DATEVALUE(Specific_Date_Time__c))) + ':' +
    TEXT(DAY(DATEVALUE(Specific_Date_Time__c))),
    '1:1',   1,
    '4:18',  1,
    '5:26',  1,
    '7:4',   1,
    '9:1',   1,
    '11:27', 1,
    '12:25', 1,
    0
) = 1

Advertisements

Text Link: Map this Account

This is a cool little text link to locate the Account using one of two different mapping websites and the Account Billing Address. All with a single click!

  1. Setup | Build | Customize | Accounts | Buttons, Links, and Actions | New Button or Link
  2. Name: Map this Account
  3. Display Type: Detail Page Link (creates a text link)
  4. Behavior: Display in new window (opens in a new browser tab)
  5. Content Source: URL
  6. Copy & paste code below for desired website
  7. Click Save
  8. Add to page layouts: Setup | Build | Customize | Accounts | Page Layouts | Edit (the appropriate pages)
  9. Click Custom Links
  10. Drag and drop Map this Account to the Custom Links section of the page
  11. Click Save

Code for Google Maps:
http://maps.google.com/maps?f=q&hl=en&q=!Account_BillingStreet+!Account_BillingCity+!Account_BillingState&om=1

Code for MapQuest:
http://www.mapquest.com/maps?address=!Account.BillingStreet&city=!Account.BillingCity&state=!Account.BillingState&zipcode=!Account.BillingPostalCode

Validation Rule: 1 Picklist and 1 Currency Field

Criteria:
If currency field Contracted Sales Volume_c is blank, AND
picklist field Time Period_c = Initial, Renewal, Extension, or Open Ended – Terms
Require: Contracted Sales Volume_c

Formula:
ISBlank(Contracted_Sales_Volume__c)&& OR( ISPICKVAL( Time_Period__c , “Initial”) ,ISPICKVAL(Time_Period__c , “Renewal”),ISPICKVAL(Time_Period__c , “Extension”),ISPICKVAL(Time_Period__c , “Open Ended – Terms”))

Calculate the Ultimate Contract Expiration Date

My client utilizes contracts and some of those contracts allow for renewals and those renewals can vary in length. We needed to be able to calculate the ultimate expiration date for all contracts.

Requirement: Determine the Ultimate Contract Expiration Date
The math: Contract End Date multiplied by Renewals Allowed_c multiplied by Length of Each Renewal_c

Formula Return Type: Date

Formula:
EndDate + ((Renewals_Allowed__c * Length_of_Each_Renewal__c) * (365.25 / 12))

Thank you, SteveMo!

Calculate Date: Ultimate Expiration Date minus Owner Expiration Notice

Requirement:
If Arrangement Type_c equals ‘Contract’
Calculate a Re-Bid Date_c which is the Ultimate Expiration Date_c minus Owner Expiration Notice.

Here’s the formula that worked:

IF( ISPICKVAL(Arrangement_Type__c, ‘Contract’) , Ultimate_Expiration_Date__c – CASE(OwnerExpirationNotice,”15 Days”, 15,
“30 Days”, 30,
“45 Days”, 45,
“60 Days”, 60,
“90 Days”, 90,
“120 Days”, 120,Null) , Null)

If you’d like to see how I ended up here and the conversation posted on Answers, visit http://bit.ly/1d2Eq5A

Much thanks to Jeff May, Damien Owen, and Andrew Johnson for taking the time and providing the guidance/solution.