2

I have a requirement where a checkbox is to be checked if a record is created by a specific profile this week on activity. So, I got the following formula:

IF(CreatedBy.Profile.Name = "Territory Sales Manager" && DateValue(CreatedDate) = THIS_WEEK, True, False)

In the above formula, THIS_WEEK is not working in formula field. I'm receiving

" Error: Incorrect parameter type for operator =. Expected Date, received Text" error.

Can anyone help me out with this createdDate issue so that I can extract this week's record along with the profile name?

  • 1
    I think the problem here is that you do not know the value of 'This week'. Even if their would be such an constant. Since this value would change every day your formula would be always true (if the name is equals to 'Territory Sales Manager') – utm Aug 29 '18 at 9:23
  • @utm, I would argue your point about the formula always returns true as long as the profile part is true. What if the CreatedDate is two weeks from Today, then obviously it wouldn't fall into THIS_WEEK range, hence giving false as an output. – Eduard Aug 29 '18 at 9:31
  • @Eduard You are right about this – utm Aug 29 '18 at 9:39
3

This is because of the data type of DateValue(CreatedDate) is date and you are comparing it with the text "THIS_WEEK".

follow @eduard suggestion to directly use CreatedDate instead of DateValue(CreatedDate).

Currently, there is no standard formula for Calculating the current week. An idea has been created, you can upvote it. The idea Link is in below Reference.

In the same, they have provided a custom solution to it.

it depends on the current day, and your definition of the current week(sun to sat, or mon to sun).

so you can play with this formula:

case(mod( today()  - DATE(1985,6,24),7),
6, if( date_field__c - today() <5,  if( date_field__c - today() >=-1, "This week",  if( today() -  date_field__c >=9, "previous Week", "other")),  if( date_field__c - today() <11, "next week", "other")),
0,  if( date_field__c - today() <6,  if( date_field__c - today() >=0, "This week",  if( today() -  date_field__c >=6, "previous Week", "other")),  if( date_field__c - today() <13, "next week", "other")),
1,  if( date_field__c - today() <5,  if( date_field__c - today() >=-1, "This week",  if( today() -  date_field__c >=9, "previous Week", "other")),  if( date_field__c - today() <11, "next week", "other")),
2, if( date_field__c - today() <5,  if( date_field__c - today() >=-1, "This week",  if( today() -  date_field__c >=9, "previous Week", "other")),  if( date_field__c - today() <11, "next week", "other")),
3, if( date_field__c - today() <5,  if( date_field__c - today() >=-1, "This week",  if( today() -  date_field__c >=9, "previous Week", "other")),  if( date_field__c - today() <11, "next week", "other")),
4, if( date_field__c - today() <5,  if( date_field__c - today() >=-1, "This week",  if( today() -  date_field__c >=9, "previous Week", "other")),  if( date_field__c - today() <11, "next week", "other")),
5, if( date_field__c - today() <5,  if( date_field__c - today() >=-1, "This week",  if( today() -  date_field__c >=9, "previous Week", "other")),  if( date_field__c - today() <11, "next week", "other")),
"other")

No syntax errors in merge fields or functions. (Compiled size: 2,345 characters)

you need to adjust the numbers in the formula.

the mod function in the case function is to check what week day today is:

case(mod(today() - DATE(1985,6,24),7),
6, "Sunday", 
0, "Monday", 
1, "Tuesday", 
2, "Wednesday", 
3, "Thursday", 
4, "Friday", 
5, "Saturday", "") 

Reference:- https://success.salesforce.com/ideaView?id=08730000000KFAU

3

Unfortunatelly, Date literals are not supported in formulas. So you need to find the range for this week in another way.

And you don't need to use the DateValue(CreatedDate) formula because CreatedDate is already of the DateTime type.

You may also find it helpful to review Examples of Advanced Formula Fields and Sample Date Formulas in particular

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.