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
ArtWArtW 

Calculation That Counts Records as "This Week" Even If Month Changes

I need help updating a custom field called “Applications Taken this Week”.  This formula field needs to include a value of "1" on any opportunities where the custom field “Appointment Set/Assigned” field (code is Appointment_Scheduled__c) is a date within the current week.  The problem with my current formula, is that it’s looking to match the Year and Month, then seeing if the date is within the current week by looking at “today as a weekday number”.  When we changed from August to September this week, the formula stopped including all the leads from earlier this week because the month didn’t match.  I need a way to show a 1 in this field anytime the date takes place within the current week, regardless of what month we're. 

 

Here is my current formula:

 

IF(AND( YEAR( Appointment_Scheduled__c )=YEAR(TODAY()),MONTH( Appointment_Scheduled__c )=MONTH(TODAY()), (Appointment_Scheduled__c ) >
TODAY()- Today_as_a_Weekday_Number__c, Appointment_Scheduled__c < (TODAY()+1)
),1,0)

 

 

SporterSporter

 

IF(
AND(
YEAR( Appointment_Scheduled__c )=YEAR(TODAY()),
(IF(DAY(TODAY())<=6, 1,0) || MONTH( Appointment_Scheduled__c )=MONTH(TODAY())),(Appointment_Scheduled__c ) > TODAY()- Today_as_a_Weekday_Number__c, Appointment_Scheduled__c < (TODAY()+1)
),1,0)

 If you add in a clause like above this might help, trouble with testing this is you would have to wait for the end of the month/start of the new month to do it. So this clause checks if the Day is between 1 and 6 (so possiblity of being in the previous month) and provieds a true result to that part of the AND function even if the MONTH() function for Today doesn't return the previous month.

 

Tell me if this helps otherwise if it doesn't if you can give me the formula for the weekday calculation also I'll look into this more.

 
ArtWArtW

Thanks .. I'll keep you posted.