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?

Improve this question
3
  • 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, 2018 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, 2018 at 9:31
  • @Eduard You are right about this
    – utm
    Aug 29, 2018 at 9:39

2 Answers 2

Reset to default
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

Improve this answer
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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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