Salesforce Developer Community

Chatter

Feed
0

Best Answers
0

Likes Received
0

Likes Given
1

Questions
1

Replies

MOD(Today(),7) gives error trying to determine the day of the week

I'm creating a custom object where we will put schedules for client reports we need to run on a weekly, bi-weekly, or monthly basis. Currently we are using recurring scheduled tasks, but I'd like to get away from that for various reasons.

For Weekly schedules, I simply want the custom date formula to show the previous Sunday date of the given week based on Today(). The problem is the MOD() function does not directly translate the Today() date into a date value and yields an error. I've seen cases where MOD(Today() - DateValue(createddate),7) works, however MOD(Today(),7) errors out.

This is the code as I imagined it should be:

Today() - MOD(Today(),7) + 1

Any guidance is greatly appreciated.

nycsalesops
November 16, 2011
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