function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Austin GutzAustin Gutz 

Formula field: Closed Date = LAST WEEK

I'm trying to build a formula (boolean) that's checked "TRUE" if the Close Date on the record was for LAST WEEK. 
jigarshahjigarshah
CloseDate >= (TODAY() - 7)

Please do not forget to mark this thread as SOLVED and answer as the BEST ANSWER if it helps address your issue. 
Alain CabonAlain Cabon
Hi,

The complete formula for Last Week - Next Week is much more complex:

Here is the Final Implementation based on ChuankaiZhou's framework would be like below by Gunish Chawla.

According the day of week and the difference of days, you can evaluate the last/next week. 
case(mod(today() - DATE(1985,6,24),7),
6, if(Insertion_Date__c - today() <=0, if(Insertion_Date__c - today() >-7,"This week",if(today() -   Insertion_Date__c <=14,"Previous Week","Other")),if(Insertion_Date__c - today() <8,"Next Week","Other")), 
0, if(Insertion_Date__c - today() <=7, if(Insertion_Date__c - today() >-1,"This week",if(today() -   Insertion_Date__c <=8,"Previous Week","Other")),if(Insertion_Date__c - today() <15,"Next Week","Other")), 
1, if(Insertion_Date__c - today() <=6, if(Insertion_Date__c - today() >-2,"This week",if(today() -   Insertion_Date__c <=9,"Previous Week","Other")),if(Insertion_Date__c - today() <14,"Next Week","Other")), 
2, if(Insertion_Date__c - today() <=5, if(Insertion_Date__c - today() >-3,"This week",if(today() -   Insertion_Date__c <=10,"Previous Week","Other")),if(Insertion_Date__c - today() <13,"Next Week","Other")), 
3, if(Insertion_Date__c - today() <=4, if(Insertion_Date__c - today() >-4,"This week",if(today() -   Insertion_Date__c <=11,"Previous Week","Other")),if(Insertion_Date__c - today() <12,"Next Week","Other")), 
4, if(Insertion_Date__c - today() <=3, if(Insertion_Date__c - today() >-5,"This week",if(today() -   Insertion_Date__c <=12,"Previous Week","Other")),if(Insertion_Date__c - today() <11,"Next Week","Other")), 
5, if(Insertion_Date__c - today() <=2, if(Insertion_Date__c - today() >-6,"This week",if(today() -   Insertion_Date__c <=13,"Previous Week","Other")),if(Insertion_Date__c - today() <10,"Next Week","Other")),
 "")  = "Previous Week"

mod(today() - DATE(1985,6,24),7)  => 24 June 1985 is a Monday

mod(today() - DATE(1985,6,24),7) = 0 if today is a Monday
​mod(today() - DATE(1985,6,24),7) = 1 if today is a Tuesday
​mod(today() - DATE(1985,6,24),7) = 2 if today is a Wednesday

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

For each day of week and according the difference of days ( if(ClosedDate__c - today() <=2  etc) you can calculate the next/previous or other week.

It could be interesting to simplify the formula for only "LAST WEEK".

This formula is huge and works for dates. If you have to use "DATEVALUE( )" each time for a date time, that could exceed very quickly the size limit for the compiled formula.

It is not an easy problem in fact even if the principle is quite simple.

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

Regards
Alain CabonAlain Cabon
If you have got already a solution from an other forum, please post the final solution (it is an interesting problem).
Austin GutzAustin Gutz
Alain - This works almost perfectly! I had seen the post you linked but didn't think it would work for my needs (I was wrong)

Here is what I have 
IF(
case(mod(today() - DATE(1985,6,24),7),
6, if(Closed_Date_for_Won_Last_Week__c - today() <=0, if(Closed_Date_for_Won_Last_Week__c - today() >-7,"This week",if(today() -   Closed_Date_for_Won_Last_Week__c <=14,"Previous Week","Other")),if(Closed_Date_for_Won_Last_Week__c - today() <8,"Next Week","Other")), 
0, if(Closed_Date_for_Won_Last_Week__c - today() <=7, if(Closed_Date_for_Won_Last_Week__c - today() >-1,"This week",if(today() -   Closed_Date_for_Won_Last_Week__c <=8,"Previous Week","Other")),if(Closed_Date_for_Won_Last_Week__c - today() <15,"Next Week","Other")), 
1, if(Closed_Date_for_Won_Last_Week__c - today() <=6, if(Closed_Date_for_Won_Last_Week__c - today() >-2,"This week",if(today() -   Closed_Date_for_Won_Last_Week__c <=9,"Previous Week","Other")),if(Closed_Date_for_Won_Last_Week__c - today() <14,"Next Week","Other")), 
2, if(Closed_Date_for_Won_Last_Week__c - today() <=5, if(Closed_Date_for_Won_Last_Week__c - today() >-3,"This week",if(today() -   Closed_Date_for_Won_Last_Week__c <=10,"Previous Week","Other")),if(Closed_Date_for_Won_Last_Week__c - today() <13,"Next Week","Other")), 
3, if(Closed_Date_for_Won_Last_Week__c - today() <=4, if(Closed_Date_for_Won_Last_Week__c - today() >-4,"This week",if(today() -   Closed_Date_for_Won_Last_Week__c <=11,"Previous Week","Other")),if(Closed_Date_for_Won_Last_Week__c - today() <12,"Next Week","Other")), 
4, if(Closed_Date_for_Won_Last_Week__c - today() <=3, if(Closed_Date_for_Won_Last_Week__c - today() >-5,"This week",if(today() -   Closed_Date_for_Won_Last_Week__c <=12,"Previous Week","Other")),if(Closed_Date_for_Won_Last_Week__c - today() <11,"Next Week","Other")), 
5, if(Closed_Date_for_Won_Last_Week__c - today() <=2, if(Closed_Date_for_Won_Last_Week__c - today() >-6,"This week",if(today() -   Closed_Date_for_Won_Last_Week__c <=13,"Previous Week","Other")),if(Closed_Date_for_Won_Last_Week__c - today() <10,"Next Week","Other")),
 "") 
= "Previous Week",
True,
False
)

The only problem I'm having is a date from Sunday - Sunday is evaluating as "true". 

Example:
Today is 9/1
A close date of 8/20-8/27 is getting set as "Previous Week". For my purpose, 8/27 should not be equal to the previous week (8/21-8/27).
Alain CabonAlain Cabon
Hi Austin,

Just change the reference DATE(1985,6,23) = Sunday  
case(mod(today__c - DATE(1985,6,23),7), 
0, if(Closed_Date_for_Won_Last_Week__c - today__c <=7, if(Closed_Date_for_Won_Last_Week__c - today__c >-1,"This week",if(today__c - Closed_Date_for_Won_Last_Week__c <=8,"Previous Week","Other")),if(Closed_Date_for_Won_Last_Week__c - today__c <15,"Next Week","Other")), 
1, if(Closed_Date_for_Won_Last_Week__c - today__c <=6, if(Closed_Date_for_Won_Last_Week__c - today__c >-2,"This week",if(today__c - Closed_Date_for_Won_Last_Week__c <=9,"Previous Week","Other")),if(Closed_Date_for_Won_Last_Week__c - today__c <14,"Next Week","Other")), 
2, if(Closed_Date_for_Won_Last_Week__c - today__c <=5, if(Closed_Date_for_Won_Last_Week__c - today__c >-3,"This week",if(today__c - Closed_Date_for_Won_Last_Week__c <=10,"Previous Week","Other")),if(Closed_Date_for_Won_Last_Week__c - today__c <13,"Next Week","Other")), 
3, if(Closed_Date_for_Won_Last_Week__c - today__c <=4, if(Closed_Date_for_Won_Last_Week__c - today__c >-4,"This week",if(today__c - Closed_Date_for_Won_Last_Week__c <=11,"Previous Week","Other")),if(Closed_Date_for_Won_Last_Week__c - today__c <12,"Next Week","Other")), 
4, if(Closed_Date_for_Won_Last_Week__c - today__c <=3, if(Closed_Date_for_Won_Last_Week__c - today__c >-5,"This week",if(today__c - Closed_Date_for_Won_Last_Week__c <=12,"Previous Week","Other")),if(Closed_Date_for_Won_Last_Week__c - today__c <11,"Next Week","Other")), 
5, if(Closed_Date_for_Won_Last_Week__c - today__c <=2, if(Closed_Date_for_Won_Last_Week__c - today__c >-6,"This week",if(today__c - Closed_Date_for_Won_Last_Week__c <=13,"Previous Week","Other")),if(Closed_Date_for_Won_Last_Week__c - today__c <10,"Next Week","Other")), 
6, if(Closed_Date_for_Won_Last_Week__c - today__c <=1, if(Closed_Date_for_Won_Last_Week__c - today__c >-7,"This week",if(today__c - Closed_Date_for_Won_Last_Week__c <=14,"Previous Week","Other")),if(Closed_Date_for_Won_Last_Week__c - today__c <9,"Next Week","Other")), 
"") 
= "Previous Week"

By the way, I displace "6," at the end with more logical numbers but the first formula could be better.
(also by the way, Salesforce recommends simpler formulas if the result is true or false like above)

Just change the reference date for the first date of the week.

DATE(1985,6,24) = Monday
DATE(1985,6,23) = Sunday 

... if I understood your question correctly.

Regards
Alain
Alain CabonAlain Cabon
Just change the reference date for the first day of the week.
Austin GutzAustin Gutz
Even changing the reference date is still giving me an issue. It seems 8 days are being considered for each value (Other, Previous Week, Next week).

If I change the reference DATE to (1985,6,23) then the week is starting on a Sunday. I want our weeks to start on Monday (1985,6,24) but that still marks Sunday-Sunday as "Previous Week". 

I should see:


8/27/2017 = Other <-- Using (1985,6,24) marks this as Last Week
8/28/2017 = Last Week
8/29/2017 = Last Week
8/30/2017 = Last Week
8/31/2017 = Last Week
9/1/2017 = Last Week
9/2/2017 = Last Week
9/3/2017 = Last Week <-- Using (1985,6,23) marks this as This Week​
9/4/2017 = This Week 

Not sure what I'm missing here. 
Alain CabonAlain Cabon

Hi Austin,

With your last explanations, we can change the numbers in the formula. 

Today: Thuesday 9/5/2017

0) Monday: 9/4 : This Week  : 0
1) Sunday : 9/3​ : Last Week:  -1 if the week starts on Monday (0)
2) Saturday: 9/2 : Last Week : -2
3) Friday: 9/1 : Last Week : -3
4) Thurday: 8/31 : -4
5) Wednesday 8/30 : -5
6) Thuesday 8/29 : -6
7) Monday: 8/28 : Last Week  -7
8) Sunday: 8/27 : Last Week: -8
9) Saturday: 8/26 : Other -9

-1 to -8 : Last Week
-9 to -15 : Other

case(mod(today__c - DATE(1985,6,24),7), 
 

case(mod(today__c - DATE(1985,6,24),7),
0, if(Closed_Date_for_Won_Last_Week__c - today__c <=8,if(Closed_Date_for_Won_Last_Week__c - today__c >-1,"This week",if(today__c - Closed_Date_for_Won_Last_Week__c <=8,"Previous Week","Other")),if(Closed_Date_for_Won_Last_Week__c - today__c <16,"Next Week","Other")),
1, if(Closed_Date_for_Won_Last_Week__c - today__c <=7, if(Closed_Date_for_Won_Last_Week__c - today__c >-2,"This week",if(today__c -Closed_Date_for_Won_Last_Week__c <=9,"Previous Week","Other")),if(Closed_Date_for_Won_Last_Week__c - today__c <15,"Next Week","Other")), 
2, if(Closed_Date_for_Won_Last_Week__c - today__c <=6, if(Closed_Date_for_Won_Last_Week__c - today__c >-3,"This week",if(today__c -Closed_Date_for_Won_Last_Week__c <=10,"Previous Week","Other")),if(Closed_Date_for_Won_Last_Week__c - today__c <14,"Next Week","Other")), 
3, if(Closed_Date_for_Won_Last_Week__c - today__c <=5, if(Closed_Date_for_Won_Last_Week__c - today__c >-4,"This week",if(today__c - Closed_Date_for_Won_Last_Week__c <=11,"Previous Week","Other")),if(Closed_Date_for_Won_Last_Week__c - today__c <13,"Next Week","Other")), 
4, if(Closed_Date_for_Won_Last_Week__c - today__c <=4, if(Closed_Date_for_Won_Last_Week__c - today__c >-5,"This week",if(today__c - Closed_Date_for_Won_Last_Week__c <=12,"Previous Week","Other")),if(Closed_Date_for_Won_Last_Week__c - today__c <12,"Next Week","Other")), 
5, if(Closed_Date_for_Won_Last_Week__c - today__c <=3, if(Closed_Date_for_Won_Last_Week__c - today__c >-6,"This week",if(today__c - Closed_Date_for_Won_Last_Week__c <=13,"Previous Week","Other")),if(Closed_Date_for_Won_Last_Week__c - today__c <11,"Next Week","Other")),
6, if(Closed_Date_for_Won_Last_Week__c - today__c <=2, if(Closed_Date_for_Won_Last_Week__c - today__c >-7,"This week",if(today__c -Closed_Date_for_Won_Last_Week__c <=14,"Previous Week","Other")),if(Closed_Date_for_Won_Last_Week__c - today__c <10,"Next Week","Other")), 
 "")

Regards
Alain
Ross NeillRoss Neill
With WEEKDAY function this is much easier:

IF( AND(Drop_Date__c > (TODAY()-( WEEKDAY( TODAY() ) +7 ) ),
              Drop_Date__c <= (TODAY() - WEEKDAY( TODAY() )  )
              ), 
    TRUE, 
    FALSE)
BillScottBillScott
@Ross Neill
Thanks! I modified that and I believe this works for THIS WEEK:

IF(
AND(
Drop_Date__c < (TODAY() + 8 - ( WEEKDAY( TODAY() ) )),
Drop_Date__c >= (TODAY() - ((WEEKDAY( TODAY() ) -1 )))
),
TRUE,
FALSE)
TEJASWI PATILTEJASWI PATIL
Data type - Formula
Return Type - Checkbox

AND
(
    CloseDate >= (TODAY() - MOD(TODAY() - DATE(1900, 1, 7), 7) - 6),
    CloseDate <= (TODAY() - MOD(TODAY() - DATE(1900, 1, 7), 7))
)

This formula indicates the period from last Sunday to Saturday as true.