Mixing
Formula field to get records created this week on activities
Clash Royale CLAN TAG#URR8PPP
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;
up vote
2
down vote
favorite
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?
formula-field validation-rule
edited Aug 29 at 14:18
battery.cord
6,26951742
asked Aug 29 at 9:05
Sastri
113
add a comment |Â
up vote
2
down vote
favorite
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?
formula-field validation-rule
edited Aug 29 at 14:18
battery.cord
6,26951742
asked Aug 29 at 9:05
Sastri
113
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 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 at 9:31
@Eduard You are right about this
– utm
Aug 29 at 9:39
add a comment |Â
up vote
2
down vote
favorite
up vote
2
down vote
favorite
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?
formula-field validation-rule
edited Aug 29 at 14:18
battery.cord
6,26951742
asked Aug 29 at 9:05
Sastri
113
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?
formula-field validation-rule
edited Aug 29 at 14:18
battery.cord
6,26951742
asked Aug 29 at 9:05
Sastri
113
edited Aug 29 at 14:18
battery.cord
6,26951742
edited Aug 29 at 14:18
battery.cord
6,26951742
edited Aug 29 at 14:18
battery.cord
6,26951742
6,26951742
asked Aug 29 at 9:05
Sastri
113
asked Aug 29 at 9:05
Sastri
113
asked Aug 29 at 9:05
Sastri
113
113
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 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 at 9:31
@Eduard You are right about this
– utm
Aug 29 at 9:39
add a comment |Â
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 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 at 9:31
@Eduard You are right about this
– utm
Aug 29 at 9:39
1
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 at 9:23
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 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 at 9:31
@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 at 9:31
@Eduard You are right about this
– utm
Aug 29 at 9:39
@Eduard You are right about this
– utm
Aug 29 at 9:39
add a comment |Â
2 Answers
2
active
oldest
votes
up vote
3
down vote
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
answered Aug 29 at 9:24
sanket kumar
1,417117
add a comment |Â
up vote
3
down vote
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
answered Aug 29 at 9:21
Eduard
1,593521
add a comment |Â
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
3
down vote
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
answered Aug 29 at 9:24
sanket kumar
1,417117
add a comment |Â
up vote
3
down vote
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
answered Aug 29 at 9:24
sanket kumar
1,417117
add a comment |Â
up vote
3
down vote
up vote
3
down vote
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
answered Aug 29 at 9:24
sanket kumar
1,417117
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
answered Aug 29 at 9:24
sanket kumar
1,417117
answered Aug 29 at 9:24
sanket kumar
1,417117
answered Aug 29 at 9:24
sanket kumar
1,417117
answered Aug 29 at 9:24
sanket kumar
1,417117
1,417117
add a comment |Â
add a comment |Â
up vote
3
down vote
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
answered Aug 29 at 9:21
Eduard
1,593521
add a comment |Â
up vote
3
down vote
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
answered Aug 29 at 9:21
Eduard
1,593521
add a comment |Â
up vote
3
down vote
up vote
3
down vote
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
answered Aug 29 at 9:21
Eduard
1,593521
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
answered Aug 29 at 9:21
Eduard
1,593521
edited Aug 29 at 9:26
answered Aug 29 at 9:21
Eduard
1,593521
answered Aug 29 at 9:21
Eduard
1,593521
answered Aug 29 at 9:21
Eduard
1,593521
1,593521
add a comment |Â
add a comment |Â
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsalesforce.stackexchange.com%2fquestions%2f230471%2fformula-field-to-get-records-created-this-week-on-activities%23new-answer', 'question_page');
);
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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 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 at 9:31
@Eduard You are right about this
– utm
Aug 29 at 9:39