Salesforce Developer Community

Chatter

Feed
15

Best Answers
1

Likes Received
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!

diwoman
June 25, 2009
Like
0

SOLVED

18 replies

Date Formula

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.

Thanks in advance.

dishotton
June 01, 2009
Like
0

SOLVED

9 replies

Calculate anniversary dates from birthdate

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??

TigerPower
January 07, 2009
Like
0

SOLVED

3 replies

Advanced Formula Help - Number of Days between dates

In opportunites, I am creating a Custom field

Choosing "Formula" for field type

Choosing return a "Number" for the value

Selecting Advance Formula

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

It's not working no matter what I have tried. Please help asap

rahworks
January 07, 2009
Like
0

SOLVED

4 replies

Expiary month / year needed from formula

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

sonyas
December 10, 2008
Like
0

SOLVED

2 replies

IF(AND FORMULA QUESTION

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.

LJHOWE
November 13, 2008
Like
0

SOLVED

2 replies

HELP with validation rule frustration

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)
)

Could someone please help?

Local Edge
November 13, 2008
Like
0

SOLVED

3 replies

IF statements and Checkboxes

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

JCD@class
November 13, 2008
Like
0

SOLVED

6 replies

Assistance getting custom rule to work

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" )
)

Local Edge
November 12, 2008
Like
0

SOLVED

7 replies

Calculate a date using formula field

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))

Harmpie
November 12, 2008
Like
0

SOLVED

2 replies

How do i get Expiration date.

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

sprn
August 15, 2008
Like
0

SOLVED

9 replies

Calulating Anniversary Date

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

Paul F.ax382
August 11, 2008
Like
0

SOLVED

7 replies

Using IF function with a date formula...

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

Buell
September 25, 2009
Like
0

6 replies

Work Week Number Within a Year Date Calculation

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

Buell
August 19, 2009
Like
1

2 replies

Set Variables?

Does anyone know if it is possible to set variables in a formula?

Buell
June 26, 2009
Like
0

SOLVED

3 replies

Conditional Tabbed Objects

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?

Thanks in advance!

Buell
June 26, 2009
Like
0

0 replies

Reference record owner's custom user fields?

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.

Buell
December 11, 2008
Like
0

0 replies

Making Tabbed Accounts Optional

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.

Buell
November 18, 2008
Like
0

4 replies

Tabbed Record & Inline Editing

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?

Buell
November 14, 2008
Like
0

5 replies

Can anyone shorten this formula?

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

Buell
October 02, 2008
Like
0

0 replies

Next Renewal Date Field Part 2

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?

Buell
September 29, 2008
Like
0

SOLVED

4 replies

Next Renewal Date Field... Help!

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?

Thanks in advance.

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

Buell
September 26, 2008
Like
0

SOLVED

2 replies

Formula field on account, dependent on associated opportunities?

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!

Buell
September 05, 2008
Like
0

1 replies

Forcing Queue Ownership on Leads

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!

Buell
August 29, 2008
Like
0

SOLVED

3 replies

Please help me shrink the formula.

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

Buell
August 07, 2008
Like
0

SOLVED

3 replies

Created Day of the Week

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.

Buell
June 30, 2008
Like
0

6 replies

Work Week Number Within a Year Date Calculation

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.

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

Buell
August 19, 2009
Like
1

SOLVED

7 replies

Using IF function with a date formula...

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

Buell
September 25, 2009
Like
0

3 replies

Help with a Lead/Sale Formula

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.

CDanielson
August 19, 2009
Like
0

3 replies

Want to build a formula field that calculates a total number of hours

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).

Gina199
July 01, 2009
Like
0

SOLVED

3 replies

Conditional Tabbed Objects

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

Thanks in advance!

Buell
June 26, 2009
Like
0

SOLVED

21 replies

Calculating close Week based on opportunity close date

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!

diwoman
June 25, 2009
Like
0

5 replies

Subtract days from date

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.

sgamc
June 25, 2009
Like
0

SOLVED

18 replies

Date Formula

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.

Thanks in advance.

dishotton
June 01, 2009
Like
0

SOLVED

9 replies

Calculate anniversary dates from birthdate

TigerPower
January 07, 2009
Like
0