• Buell
• NEWBIE
• 385 Points
• Member since 2008

• Chatter
Feed
• 15
• 1
• 0
Likes Given
• 14
Questions
• 72
Replies

I am trying to create a formula to calculate the Week in the Year that an opportunity will close, based on the Close Date.

There was a post earlier in April on the same subject, but it remains unsolved.  I have searched the help section and blogs, but cannot create the winning formula....any help is much appreciated!

I'm looking for help on a date formula for twofields - Start Date and End Date.

Start Date = If created date between 1 and 15 of createddate month, return MM01YYYY;  if createddate between 16 and last day of created date month, return MM16YYYY.

End Date = If Start Date is MM01YYYY, returnMM15YYYY;  if Start Date is MM16YYYY,return last day of month.

These are all calendar dates.

Hey,
I'm trying to find out the upcoming key anniversary dates based on birthdate field. I have found samples that calculate age or next birthday of a person, but I would like to be able to calculate specific upcoming birthdays like 20th, 30th, 40th birthday...

Any ideas? I thought that this would have been quite simple thing to do, but it wasn't :smileysurprised:

It would be also interesting to know how to do this with months (for example: birthdate (yyyy-mm-dd) 1988-10-20
+ 240 months (=20 years)). How to find / calculate those upcoming dates??
In opportunites, I am creating a Custom field
Choosing "Formula" for field type
Choosing return a "Number" for the value
Trying to accomplish this.....
How long since the Opportunity was opened until the time it was closed Won, or closed Lost
My brain tells me to do
ClosedDate - CreateDate = Number of Days

Hi

I am trying to create a formula from the following fields:

Created Date
Term (Pick list > 1yr, 2yr, 3yr)

To give me the month & year that the policy will expire

Can anyone help?

Thank you

Sonya
• December 10, 2008
• Like
• 0
Code:
Data Type Formula
IF(AND( Region__c ="REGION 1", Agent_State__c ="MI"),"JOHN DOE",
IF(AND( Region__c ="REGION 1", Agent_State__c ="IN"),"JOHN DOE",)
I have several different regions with specific agent states to assign to my marketing reps. I'm trying to set up a formula field to look at the region and the agent state and put the name I type in for the marketing rep.  The above formula works, but it becomes too large to run.  I am open to any suggestions on this.  Thanks for any help you can give.
• November 13, 2008
• Like
• 0
I am attempting to write a code that forces a user to enter info into a test field when a specific picklist value is selected. Where my frustration lies is that I have a basically identical rule for my Leads page that works fine and when I duplicated it into my Opportunites page it doesn't.

Code:

AND (
OR(
ISPICKVAL( StageName , "Closed Lost" )),
ISNULL( Closed_Lost_Reason__c )
)

I also tried this code to no avail:

Code:
and(
ispickval(StageName , "Closed Lost")
,isnull(Closed_Lost_Reason__c)
)
Using the Formula field, is there a way to calculate how many check boxes are checked?

Here is an example. I have 3 check boxes: check1 (if this is checked, the value is 10), check2 (if this is checked, the value is 20), check3 (if this is checked, the value is 30) and a formula field: formula1.

In the formula field, I want to test how many of those 3 checkboxes are checked, and add the value for each checkbox.

So if check1 is checked, then formula1 = 10; if check2 is checked then formula1 = 20 and so on. But if check1 and check2 is selected, then formula1 = 30.

The only way I could think of is by creating a variable and adding to it for the value of formula1. Does anyone know another way using a combination of functions?

Thanks,
Daniel
I am trying to create a validation rule that specifies if a certain value is selected in one picklist field it forces the owner to make a picklist selection in another field before any changes can be saves.

Any help would be greatly appreciated.

Below is what I worked on but cannot get it to work:

AND (
OR (
ISPICKVAL (  Current_SEM__c, "Yes") ) ,
ISPICKVAL (   SEM_TYPE__c, "Local" ),
ISPICKVAL ( SEM_TYPE__c, "Specter" ),
ISPICKVAL ( SEM_TYPE__c, "PYP Company" ),
ISPICKVAL ( SEM_TYPE__c, "Other Agency" ),
ISPICKVAL ( SEM_TYPE__c, "Do It Yourself" ),
ISPICKVAL ( SEM_TYPE__c, "Lead Aggregator" ),
ISPICKVAL ( SEM_TYPE__c, "Industry Specialists" )
)
Is something like this possible using a formula field? I want to add a numberfield (number of months in 'Estimated_Time_For_Exection__c') to a start date, to calculate the end date.

The formula below gives an error:

Code:
DATE(YEAR(Start_Date__c),MONTH(Start_Date__c)+ Estimated_Time_For_Exection__c ,DAY(Start_Date__c))

I have couple of opportunity custom fields. For this purpose i need to create a new custom formula field (Expiration Date) which display it's value by adding up the value of 'Installation Date' (Date field) and Contract Term (Number field). I'm trying in many ways.

Physically it should be :

Expiration date = Installation Date + Contract Term

Logically unable to make it.

Can you throw some light on this?

Thanks.

Message Edited by sprn on 08-15-2008 04:34 PM
• August 15, 2008
• Like
• 0
First time poster & new to SFDC.

For a contact I am trying to calculate the next anniversary date based on a historical registration date (which could be many years previous), to drive a workflow for a reminder e-mail and follow up task to record owner using the Time Based work flow.

The formula that I have used previously, suggested in SFDC documentation (to calculate Next birthday), calculates correctly when using the standard contact Birthdate field, but when I apply the same formula to a custom date field the compilation exceeds the maximum 5000 characters (Error: Compiled formula is too big to execute (5,262 characters). Maximum size is 5,000 characters)

Current formula below and any suggestions on reducing formula size or alternate approaches will be appreciated greatly.

IF(MONTH(AppDate__c)>MONTH(TODAY()),DATE(YEAR(TODAY()),MONTH(AppDate__c),DAY(AppDate__c)),
IF(MONTH(AppDate__c)<MONTH(TODAY()),DATE(YEAR(TODAY())+1,MONTH(AppDate__c),DAY(AppDate__c)),
IF(DAY(AppDate__c) >= (DAY(TODAY())),DATE(YEAR(TODAY()),MONTH(AppDate__c),DAY(AppDate__c)),
DATE(YEAR(TODAY())+1,MONTH(AppDate__c),DAY(AppDate__c)))))

Regards

Paul

Not sure what is going on here.  I have a date formula:

DATE(2009, 02, 02)

I want to make it conditional on another field.  In particular if another field is 0 then I want to display a blank date.  To accomplish this I added an IF funciton:

IF( Custom_Field__c = 0, '', DATE(2009, 02, 02) )

Salesforce then throws the error:

Incorrect parameter for function IF(). Expected Text, recieved Date.

Any thought?  If I remove the function DATE() then it says it expected text and recieved a number...

Also, if I embed the function IF() within the DATE() function, I get the same error.

Message Edited by Buell on 09-25-2009 09:48 AM
• September 25, 2009
• Like
• 0

Someone asked for help on figuring out a formula to calculate what work week a date falls within, with work weeks starting on Mondays, and I thought I'd share.

((CloseDate - DATE(YEAR(CloseDate), 1, 1) + (CASE( MOD( DATE( YEAR( CloseDate ), 01, 01 ) - DATE(1900, 1, 7), 7),
0, - 0,
6, - 1,
2, + 2,
4, + 4,
5, + 5,
1, + 1,
+ 3))) / 7) + IF(MOD((CloseDate - DATE(YEAR(CloseDate), 1, 1) + (CASE( MOD( DATE( YEAR( CloseDate ), 01, 01 ) - DATE(1900, 1, 7), 7),
0, - 0,
6, - 1,
2, + 2,
4, + 4,
5, + 5,
1, + 1,
+ 3))), 7) = 0, 0, 1)

Just change CloseDate to the date field in question.
Message Edited by Buell on 08-20-2009 04:39 PM
Message Edited by Buell on 08-20-2009 04:43 PM
• August 19, 2009
• Like
• 1
Does anyone know if it is possible to set variables in a formula?
• June 26, 2009
• Like
• 0

I have created tabbed pages with accounts, leads, contacts etc and would like to give users the option to either use these new visualforce pages or stick with the standard layouts.  Looking through the force.com cookbook I came across the following:

<apex:page action= "{!if(\$Profile.Name !='System Administrator', null, urlFor(\$Action.Account.Tab, \$ObjectType.Account, null, true))}" ... >

Rather than making it conditional on ones profile I'd like to make it conditional on a custom field on the user record called 'Tabbed_Objects__c'.  Can anyone help me modify the above to show the standard account or tabbed account based on this checkbox?

• June 26, 2009
• Like
• 0
Does anybody know how to reference a record owners user fields?  I have a custom field for each user record and want to apply it to opportunity records they own in a formula.  Any help is appreciated.
• December 11, 2008
• Like
• 0
I would like to have a checkbox on the user record that is checked if they want tabbed account/leads/oppty views.

Would it be possible to create a visualforce page that would display the records appropriately based on this checkbox?  At this point half of my sales team wants tabbed records, and the other half hates them.  Any help is appreciated.
• November 18, 2008
• Like
• 0
Is it possible to have inline editing with a visualforce page?  I have created tabbed account and tabbed opportunity pages but without being able to edit by double clicking on fields it is almost pointless.  Any ideas on how to get it to work?  Should it work?
• November 14, 2008
• Like
• 0
Code:
DATE (
YEAR(CloseDate) + FLOOR((((FLOOR((((YEAR( TODAY() ) * 12) + MONTH( TODAY() )) - ((YEAR( CloseDate ) * 12) + MONTH( CloseDate ))) / Term_in_Months__c) + 1) * Term_in_Months__c) + (MONTH(CloseDate) - 1)) / 12),
CASE(MOD(((FLOOR((((YEAR( TODAY() ) * 12) + MONTH( TODAY() )) - ((YEAR( CloseDate ) * 12) + MONTH( CloseDate ))) / Term_in_Months__c) + 1) * Term_in_Months__c) + MONTH(CloseDate),12),
1,01,
2,02,
3,03,
4,04,
5,05,
6,06,
7,07,
8,08,
9,09,
10,10,
11,11,
12),
IF( AND(DAY(CloseDate) = 29,MONTH(CloseDate) = 02) , 28, DAY(CloseDate)))

Compiles to 18,869 characters, references no custom formula fields.

Message Edited by Buell on 10-02-2008 02:23 PM
• October 02, 2008
• Like
• 0
OK now I need some help.  So our opportunities have a contract length field (in years), the standard close date field and a next renewal date field which pulls from the previous two to find out when the next renewal date is.  Here's the formula for the next renewal date field:

Code:
DATE(
YEAR ( CloseDate ) + (  Contract_Length__c  * (FLOOR(( TODAY() - CloseDate ) / (  Contract_Length__c  * 365 ))  + 1)),
MONTH(CloseDate),
IF( AND(DAY(CloseDate) = 29,MONTH(CloseDate) = 02) , 28, DAY(CloseDate)))

I need to change the contract length field to months, and consequently change the above formula to take it into account.  Any ideas?

• September 29, 2008
• Like
• 0
We have contracts (1 to 5 years) which auto-renew unless the customer cancels.  For these renewals we keep the original opportunity record in SFDC to track.  I have created a 'renewal date field' populated off of what deal term is selected (1-5) and the closed date but the problem is with customers that have already cycled through 2 or more terms of their contract, the renewal date is now useless.

I want to have a field that calculates what the next renewal date is.  Anyone have any suggestions?

Here is what i have so far, and I think I'd be able to finish it off if it weren't for the character limitations.

Term_to_Number__c: converts 'deal term' dropdown field to it's equivalent number.

A field which determines if the current year is a renewal year:
Code:
IF((YEAR(TODAY()) = YEAR(CloseDate )),
'No',
IF ((MOD(((YEAR(TODAY()) - YEAR(CloseDate) ) / Term_to_Number__c), FLOOR(((YEAR(TODAY()) - YEAR(CloseDate) ) / Term_to_Number__c )))) = 0,
'Yes',
'No'
)
)

A field which determines how many deal terms have passed:
Code:
FLOOR ((( YEAR( TODAY() ) - YEAR( CloseDate ) ) / Term_to_Number__c ))

The next renewal year: (The problem with this one is if this year is a renewal year, it adds on one more term.  Tried to account for it with an IF() pulling on the renewal year formula above to determine whether or not to subtract a term from the total, but the formula came out too big.)
Code:
YEAR ( CloseDate ) + ( Term_to_Number__c * (Terms_Completed__c + 1))

So that is where I am.  Any ideas?

UPDATE:

changed the field which determines the terms completed to:
Code:
FLOOR(( TODAY() - CloseDate ) / ( Term_to_Number__c * 365 ))

This takes care of the issue with the "Next Renewal Year" field where it was adding another term.

Message Edited by Buell on 09-26-2008 02:35 PM

Message Edited by Buell on 09-26-2008 02:51 PM
• September 26, 2008
• Like
• 0
Is it possible to create a formula field on accounts that looks to see if there are any opportunities set to closed/won for an account?  Sorry if this has already been covered somewhere else, I'm just not quite sure what to search for in the forums, and what I did search turned up no results.  Thanks in advance!
• September 05, 2008
• Like
• 0
For a number of reasons we are trying to force lead owners to be queue's only.  I tried setting up a validation rule, but the problem lies in that you can't edit ownership in the edit page creating an impasse.  Any thought on how to enforce queue ownership on leads, other than constantly reminding forgetful people?  Thanks in advance!
• August 29, 2008
• Like
• 0
I'm wanting a formula field that shows an opportunity expiration date, calculated from a drop down that shows the term in years and the input close date.  Here is what I have, and what SFDC is saying.  Thanks in advance.

Code:
IF( ISPICKVAL( Deal_Term__c , '1 Year') , DATE( YEAR(CloseDate) + 1 , MONTH(CloseDate) , DAY(CloseDate) ) ,
IF( ISPICKVAL( Deal_Term__c , '2 Year') , DATE( YEAR(CloseDate) + 2 , MONTH(CloseDate) , DAY(CloseDate) ) ,
IF( ISPICKVAL( Deal_Term__c , '3 Year') , DATE( YEAR(CloseDate) + 3 , MONTH(CloseDate) , DAY(CloseDate) ) ,
IF( ISPICKVAL( Deal_Term__c , '4 Year') , DATE( YEAR(CloseDate) + 4 , MONTH(CloseDate) , DAY(CloseDate) ) ,
DATE( YEAR(CloseDate) + 5 , MONTH(CloseDate) , DAY(CloseDate) )))))

Compiled formula is too big to execute (6,162 characters). Maximum size is 5,000 characters.

Message Edited by Buell on 08-07-2008 01:20 PM

Message Edited by Buell on 08-07-2008 01:20 PM
• August 07, 2008
• Like
• 0
Does anyone know a formula to show the day of the week a lead was created (ie monday, tuesday, etc.)?  I want to setup a workflow that will notify management when a lead has not been contacted within 24 hours of being created.  The problem lies in leads that are submitted over the weekend.
• June 30, 2008
• Like
• 0

Someone asked for help on figuring out a formula to calculate what work week a date falls within, with work weeks starting on Mondays, and I thought I'd share.

((CloseDate - DATE(YEAR(CloseDate), 1, 1) + (CASE( MOD( DATE( YEAR( CloseDate ), 01, 01 ) - DATE(1900, 1, 7), 7),
0, - 0,
6, - 1,
2, + 2,
4, + 4,
5, + 5,
1, + 1,
+ 3))) / 7) + IF(MOD((CloseDate - DATE(YEAR(CloseDate), 1, 1) + (CASE( MOD( DATE( YEAR( CloseDate ), 01, 01 ) - DATE(1900, 1, 7), 7),
0, - 0,
6, - 1,
2, + 2,
4, + 4,
5, + 5,
1, + 1,
+ 3))), 7) = 0, 0, 1)

Just change CloseDate to the date field in question.
Message Edited by Buell on 08-20-2009 04:39 PM
Message Edited by Buell on 08-20-2009 04:43 PM
• August 19, 2009
• Like
• 1

Not sure what is going on here.  I have a date formula:

DATE(2009, 02, 02)

I want to make it conditional on another field.  In particular if another field is 0 then I want to display a blank date.  To accomplish this I added an IF funciton:

IF( Custom_Field__c = 0, '', DATE(2009, 02, 02) )

Salesforce then throws the error:

Incorrect parameter for function IF(). Expected Text, recieved Date.

Any thought?  If I remove the function DATE() then it says it expected text and recieved a number...

Also, if I embed the function IF() within the DATE() function, I get the same error.

Message Edited by Buell on 09-25-2009 09:48 AM
• September 25, 2009
• Like
• 0

I have been tasked to calculate how many eads it takes to win a sale. (e.g. we need to generate 200 leads to
get 1 sale). I would appreciate any help.

I need to develop a formula field that does the following:

Calculates the total number of volunteer hours the contact has worked for the current calendar year (zeros out at new year).

I have created tabbed pages with accounts, leads, contacts etc and would like to give users the option to either use these new visualforce pages or stick with the standard layouts.  Looking through the force.com cookbook I came across the following:

<apex:page action= "{!if(\$Profile.Name !='System Administrator', null, urlFor(\$Action.Account.Tab, \$ObjectType.Account, null, true))}" ... >

Rather than making it conditional on ones profile I'd like to make it conditional on a custom field on the user record called 'Tabbed_Objects__c'.  Can anyone help me modify the above to show the standard account or tabbed account based on this checkbox?

• June 26, 2009
• Like
• 0

I am trying to create a formula to calculate the Week in the Year that an opportunity will close, based on the Close Date.

There was a post earlier in April on the same subject, but it remains unsolved.  I have searched the help section and blogs, but cannot create the winning formula....any help is much appreciated!

I have a formula field that calculates a date.

I have a picklist field with numbers (representing number of days).

I need a formula that lets me take the date - days and return a date.

This simple formula did not work:

Service_End_Date__c - Cancellation_Notice__c

as it returned this error on the Cancellation_Notice_c (picklist) field:

Error: Incorrect parameter for function -(). Expected number, date, datetime, received text.

• June 25, 2009
• Like
• 0

I'm looking for help on a date formula for twofields - Start Date and End Date.

Start Date = If created date between 1 and 15 of createddate month, return MM01YYYY;  if createddate between 16 and last day of created date month, return MM16YYYY.

End Date = If Start Date is MM01YYYY, returnMM15YYYY;  if Start Date is MM16YYYY,return last day of month.

These are all calendar dates.