3

I have a date field called CloseDate in Opportunity Object and the Date of Opportunity set it as 05/26/2016. I want a formula field or any logic to get the Date of the Saturday of same week(In the above date case it should be 05/28/2016).

The date should generate based on CloseDate .One more examaple, If I give the close date is 05/17/2016 then the Logic field should display the date as 06/21/2016

I tried different ways but it didn't work. Below are one of my trials

DATE(YEAR(TODAY()), MONTH(TODAY()),
  CASE(MOD(DATE(YEAR(TODAY()),MONTH(TODAY()),1) - DATE(1900, 1, 7), 7),
    0, 2,
    1, 1,
    2, 7,
    3, 6,
    4, 5,
    5, 4,
    6, 3,
  0)
)

Ans: Its giving first Monday Date of a Month

Any help will be really helpful

0

1 Answer 1

3

Copied from here: https://help.salesforce.com/HTViewHelpDoc?id=formula_examples_dates.htm

Finding the Next Day of the Week After a Date To find the date of the next occurrence of a particular day of the week following a given Date, get the difference in the number of days of the week between a date and a day_of_week, a number 0–6 where 0 = Sunday and 6 = Saturday. By adding this difference to the current date, you’ll find the date of the day_of_week. The IF() statement in this formula handles cases where the day_of_week is prior to the day of the week of the date value (e.g. date is a Thursday and day_of_week is a Monday) by adding 7 to the difference.

CloseDate + ( day_of_week - MOD( CloseDate - DATE( 1900, 1, 7 ), 7 ) )
+ IF(    MOD( CloseDate - DATE( 1900, 1, 7 ), 7 ) >= day_of_week,   7,   0 )

You can substitute either a constant or another field in for the day_of_week value based on your needs.

Where day of the week can be calculated this way in order to get the written name :

CASE(   MOD( CloseDate - DATE( 1900, 1, 7 ), 7 ),   0, "Sunday",   1, "Monday",   2, "Tuesday",   3, "Wednesday",   4, "Thursday",   5, "Friday",    "Saturday" )

And this way to just get the number :

MOD(DATEVALUE( CloseDate ) - DATE(1985,7,1),7)
2
  • But I tested one more scenario and Its failing to that value.. If we select the Saturday date it's giving next week saturday date instead of the same week saturday.. Alexander.... Can you check that once??
    – KSL
    May 26, 2016 at 21:33
  • 1
    Try with this CloseDate + ( day_of_week - MOD( CloseDate - DATE( 1900, 1, 7 ), 7 ) ) + IF( MOD( CloseDate - DATE( 1900, 1, 7 ), 7 ) >day_of_week, 7, 0 ) May 27, 2016 at 8:28

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.